0

I'm looking to upgrade MySQL fields from mb3 to mb4 in a large database and from reading the documentation I understand that the biggest problem is mb3 stores stuff in 3 bytes, but mb4 stores stuff in 4 bytes. So if you had a 255 character TINYTEXT you can only fit 85 3 byte characters (i.e. in mb3) or 63 4 byte characters (i.e. after you convert to mb4).

See documentation here: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html#:~:text=utf8mb3%20supports%20only%20characters%20in,of%20four%20bytes%20per%20character.

The problem I am struggling to understand is, the documentation seems to say this is going to be a problem when upgrading. However, I can't understand how this is a problem, because if you are using mb3 and you have 85 3 byte characters, when you change it to mb4, all 85 3 byte characters are still present, because even after upgrading they still only use 3 bytes.

So I tested this as well by creating an mb3 column and adding these 85 characters:

㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂㗂

Then I converted it to mb4 and all 85 characters are still present and each one is still using 3 bytes. So there is no data loss, so I don't understand what the issue would be?

1
  • Is the data type still tinytext ? Jan 12 at 4:32

1 Answer 1

1

So if you had a 255 character TINYTEXT you can only fit 85 3 byte characters (i.e. in mb3) or 63 4 byte characters (i.e. after you convert to mb4).

Depends on the character. The characters in utf8mb3 occupies from 1 to 3 bytes, accordingly in utf8mb4 they may occupy from 1 to 4 bytes. So 85-character string value may take from 86 to 256 bytes in UTF8MB3 and from 86 to 341 bytes in UTF8MB4. And if your value takes less than 256 bytes after character set convertion then converted value won't be truncated, and you won't loose the information.

So there is no data loss, so I don't understand what the issue would be?

Your original value contains the characters which takes not more than 3 bytes per character. They takes the same amount of bytes in UTF8MB4, so the amount of bytes per value is not altered.

CREATE TABLE test (
  value3 TINYTEXT CHARACTER SET utf8mb3,
  value4 TINYTEXT CHARACTER SET utf8mb4  
);
INSERT INTO test VALUES
('123', '123'),
('¹²³', '¹²³'),
('㗂㗂㗂', '㗂㗂㗂'),
(NULL, '😊😂🤣');
SELECT value3, HEX(value3), CHAR_LENGTH(value3), LENGTH(value3)
       value4, HEX(value4), CHAR_LENGTH(value4), LENGTH(value4)
FROM test;
value3 HEX(value3) CHAR_LENGTH(value3) value4 HEX(value4) CHAR_LENGTH(value4) LENGTH(value4)
123 313233 3 3 313233 3 3
¹²³ C2B9C2B2C2B3 3 6 C2B9C2B2C2B3 3 6
㗂㗂㗂 E39782E39782E39782 3 9 E39782E39782E39782 3 9
null null null null F09F988AF09F9882F09FA4A3 3 12

fiddle

4
  • So if I understand correctly, if we upgrade to mb4, it's impossible for data loss and if we don't ever use any 4 byte characters after upgrading, then we should never have any issues at all? But if we start using 4 byte characters, we need to be careful of some constraints?
    – KillerKode
    Jan 11 at 12:29
  • 1
    @KillerKode Yes, there is no character which needs different bytes in 3-byte and 4-byte UTF8. But if we start using 4 byte characters, we need to be careful of some constraints? Unclear.. if you'd use 4-byte charset then you'd use it everywhere (server, data, connection). Otherwise you may meet with collation incompatibility.
    – Akina
    Jan 11 at 12:49
  • What I mean is, even though it's a 4 byte character set, I can still use characters that are only taking up 1 byte of space for example and no more than 3 bytes - and may never need to use any that have been added in utf8mb4 (i.e. the 4 byte ones). But if we start using 4 byte characters, is that where there are potential compatibility issues? E.g. index limits getting exceeded? I can't quite understand what I need to watch out for when upgrading to mb4.
    – KillerKode
    Jan 11 at 13:35
  • 1
    Of course 4-byte charset column may be easily used for to store the characters which are shorter than 4 bytes. E.g. index limits getting exceeded? No, index always reserves maximal value size, and if it exceeds the limit then the index won't be created. So if you have an index which will exceed the limit after character set altered then you will receive an error, and ALTER TABLE will fail.
    – Akina
    Jan 11 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.