4 ms·

What's the problem with allowing VARCHAR(N) store a max of 3N bytes worst-case, but still allow the 4-byte characters to be present in the string? Sure, it mean

What's the problem with allowing VARCHAR(N) store a max of 3N bytes worst-case, but still allow the 4-byte characters to be present in the string? Sure, it means that you can only store 0.75 * N emojis in a VARCHAR(N), but that seems less bad than the alternative of silently corrupting data.

evanelias 5y ago
4-byte characters are simply not valid in the utf8mb3 character set. The column type defines what values may be stored in the column, and the character set is inherently part of the column type for textual data. So your question is similar to asking, what's the problem with allowing storage of negative numbers in an unsigned int column. The answer is those bytes inherently have a different meaning, or may be completely invalid, depending on the column type. > that seems less bad than the alternative of silently corrupting data MySQL has defaulted to a strict sql_mode out-of-the-box since MySQL 5.7, released over 6 years ago. With strict sql_mode, no silent corruption of data occurs. If you attempt to insert a 4-byte character into a utf8mb3 column, your insert fails with an error. Everything older than 5.7 has hit end-of-life, so there is literally no supported version of MySQL which doesn't default to enabling strict mode. The only problem is that database-as-a-service providers use nonstandard defaults, so e.g. AWS RDS doesn't come with strict mode enabled, for whatever completely insane reason. But that's technically an Amazon problem, not a MySQL problem.
- chmod600 5y ago
  "those bytes inherently have a different meaning" Are you sure? Utf-8 is a variable-width encoding so it should just work, right?
  - evanelias 5y ago
    Yes, you're correct that utf8mb3 is strictly a subset of utf8mb4, and the binary value of any valid utf8mb3 character is identical to its utf8mb4 representation. But I'd argue that's irrelevant. I meant my statement generically: the meaning of the bytes stored in a column value depend entirely on the column type, as does the range of values that may be stored or converted. If a column's type is, say, VARCHAR(100) CHARACTER SET utf8mb3, this means the column can store up to 100 utf8 characters and each character has a maximum length of 3 bytes. It is simply not legal to store 4-byte characters in this data type, just as it isn't legal to store 101 characters in it. The proposal in this subthread seems to be, instead of introducing utf8mb4, why didn't MySQL allow "utf8" (utf8mb3) columns store 4-byte characters, just without affecting the maximum number of bytes that can be stored in the column? So, a VARCHAR(100) CHARACTER SET utf8 might only be able to store max 75 characters if they're all emoji. Additionally, it would mean that the range of values allowed in a column would depend on the DB server version, and would change upon upgrading. This just seems completely counterintuitive and wrong to me, relative to how all other column type definitions work. The modern progression of MySQL has been to remove these obvious-WTF problems, not introduce more of them :) The proposal was described as "seems less bad than the alternative of silently corrupting data", but that isn't the alternative (silent data corruption doesn't occur with strict mode which is enabled by default), so this comparison doesn't make sense.
  - kevin_thibedeau 5y ago
    Everything that decodes that utf-8 has to support 21-bit codepoints. If they used a uint16_t anywhere in the processing chain then it's restricted to the BMP.

evanelias 5y ago

4-byte characters are simply not valid in the utf8mb3 character set. The column type defines what values may be stored in the column, and the character set is inherently part of the column type for textual data. So your question is similar to asking, what's the problem with allowing storage of negative numbers in an unsigned int column. The answer is those bytes inherently have a different meaning, or may be completely invalid, depending on the column type. > that seems less bad than the alternative of silently corrupting data MySQL has defaulted to a strict sql_mode out-of-the-box since MySQL 5.7, released over 6 years ago. With strict sql_mode, no silent corruption of data occurs. If you attempt to insert a 4-byte character into a utf8mb3 column, your insert fails with an error. Everything older than 5.7 has hit end-of-life, so there is literally no supported version of MySQL which doesn't default to enabling strict mode. The only problem is that database-as-a-service providers use nonstandard defaults, so e.g. AWS RDS doesn't come with strict mode enabled, for whatever completely insane reason. But that's technically an Amazon problem, not a MySQL problem.

chmod600 5y ago
"those bytes inherently have a different meaning" Are you sure? Utf-8 is a variable-width encoding so it should just work, right?
- evanelias 5y ago
  Yes, you're correct that utf8mb3 is strictly a subset of utf8mb4, and the binary value of any valid utf8mb3 character is identical to its utf8mb4 representation. But I'd argue that's irrelevant. I meant my statement generically: the meaning of the bytes stored in a column value depend entirely on the column type, as does the range of values that may be stored or converted. If a column's type is, say, VARCHAR(100) CHARACTER SET utf8mb3, this means the column can store up to 100 utf8 characters and each character has a maximum length of 3 bytes. It is simply not legal to store 4-byte characters in this data type, just as it isn't legal to store 101 characters in it. The proposal in this subthread seems to be, instead of introducing utf8mb4, why didn't MySQL allow "utf8" (utf8mb3) columns store 4-byte characters, just without affecting the maximum number of bytes that can be stored in the column? So, a VARCHAR(100) CHARACTER SET utf8 might only be able to store max 75 characters if they're all emoji. Additionally, it would mean that the range of values allowed in a column would depend on the DB server version, and would change upon upgrading. This just seems completely counterintuitive and wrong to me, relative to how all other column type definitions work. The modern progression of MySQL has been to remove these obvious-WTF problems, not introduce more of them :) The proposal was described as "seems less bad than the alternative of silently corrupting data", but that isn't the alternative (silent data corruption doesn't occur with strict mode which is enabled by default), so this comparison doesn't make sense.
- kevin_thibedeau 5y ago
  Everything that decodes that utf-8 has to support 21-bit codepoints. If they used a uint16_t anywhere in the processing chain then it's restricted to the BMP.