MySQL truncation command - unicode characters

Question

Hi all,

I am currently trying to adjust the values stored in a table in MySQL. The values stored contain a series of Unicode characters. I need to truncate to 40 bytes worth of storage, but when I try:

UPDATE `MYTABLE` SET `MYCOLUMN` = LEFT(`MYCOLUMN`, 40);

MySQL is overly helpful and retains 40 characters, rather than 40 bytes. Is there a way to get around this?

Regards and with thanks,

gaioshin

Answer 1

LEFT is byte safe.

How about modifying the column datatype so that it can only hold 40 bytes. MySQL will automatically do the truncating for you.

ALTER TABLE table_name
  MODIFY column_name column_type_40_bytes;

Answer 2

I'm not quite sure if you really want to truncate to exactly 40 bytes.

Since you can get messed up unicode characters the condition should be "MAX 40 bytes WITH the last character still being valid".

I'd write a stored function for that. I'm not sure it works, but I guess, you catch my drift:

DELIMITER &&&
CREATE FUNCTION MYTRUNCATE (subject VARCHAR(255) CHARACTER SET utf8, max_bytes INT UNSIGNED)
RETURNS VARCHAR(255) CHARACTER SET utf8
DETERMINISTIC
BEGIN
DECLARE byte_len INT;
DECLARE char_len INT;
DECLARE res VARCHAR(255) CHARACTER SET utf8;
SET res = LEFT(subject, max_bytes);
SET byte_len = BIT_LENGTH(res)/8;
SET char_len = CHAR_LENGTH(res);
WHILE byte_len > max_bytes DO
SET char_len = char_len - 1;
SET res = LEFT(res, char_len);
SET byte_len = BIT_LENGTH(res)/8;
END WHILE;
RETURN res;
END&&&
DELIMITER ;