4 ms·
VARCHAR(N) can store N characters. So with utf8mb3, that's a max of 3N bytes worst-case. But if these columns were silently/automatically converted to utf8mb4 u
by evanelias 5y ago
VARCHAR(N) can store N characters. So with utf8mb3, that's a max of 3N bytes worst-case. But if these columns were silently/automatically converted to utf8mb4 upon upgrading MySQL, they would now need to store max 4N bytes, which (with a high N) can exceed internal limits such as maximum length of an index key.
IIRC, there were additional problems in older versions of MySQL, situations where sort buffers were sized to a fixed length equal to the value's worst-case size or something like that. So sorting a large number of utf8mb4 values would use a lot more memory than utf8mb3 values (again, iirc, I might be wrong on this).
So the safer and more backwards-compatible approach was to introduce utf8mb4 as a new separate charset, and allow users to choose. MySQL 8 is now transitioning towards deprecating utf8mb3, and will finally make the utf8 alias point to utf8mb4 sometime in the near future.
That said, there are still a bunch of unpleasant uses of utf8mb3 internally in things like information_schema. I develop schema management tooling and recently lost a week to writing work-arounds for some of the more obscure ones, see https://github.com/skeema/skeema/commit/bf38edb https://github.com/skeema/skeema/commit/bf38edb :)
- lalaithion 5y agoWhat'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 ago4-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 agoYes, 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 agoEverything 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.
- chrismorgan 5y ago> they would now need to store max 4N bytes, which (with a high N) can exceed internal limits such as maximum length of an index key. Specifically, indexes are limited to 767 bytes (some say 768?), which means 191 utf8mb4 characters. So your otherwise-innocuous VARCHAR(255) primary key breaks. I’ve seen this multiple times, e.g. django-celery probably still has this: https://github.com/celery/django-celery/issues/259 https://github.com/celery/django-celery/issues/259.
- evanelias 5y agofwiw the limit is effectively 3072 bytes nowadays, default since MySQL 5.7. You could actually bump it up to 3072 in 5.5 and 5.6 too, but it required changing several settings (innodb_large_prefix, innodb_file_format, innodb_file_per_table, and the table's ROW_FORMAT).
- chrismorgan 5y agoCool, didn’t know that; thanks for the correction. I haven’t touched MySQL since 2016, and I suppose what I worked with then wasn’t 5.7, or was configured differently for historical reasons, or something.
- emmelaich 5y agoProbably just better off storing as a blob and doing all interpretation / normalization outside the db.