7 ms·

In MySQL for example, using UTF-8 charset will cause it to use 3 or 4 bytes per character. All the bytes transmitted on the wire will be UTF-8 strings, but thi

by treve 9y ago

In MySQL for example, using UTF-8 charset will cause it to use 3 or 4 bytes per character. All the bytes transmitted on the wire will be UTF-8 strings, but this is disconnected from the underlying storage engine. I don't know if this is the case for other databases, but when I read this my first thought was "This must have been MySQL"

sp332 9y ago
Oh yeah, that makes sense. At least for CHAR(36), but I'm not sure how VARCHAR(36) as given in the article would behave. (Never mind I just looked it up and it depends on the storage engine - e.g. MEMORY will always allocate the max size.)
- bitJericho 9y ago
  You'd be insane to put the primary key in a varchar. Mysql is much faster with static width tables. Your main user table should be static width for all the fields.
  - cwyers 9y ago
    As opposed to the insanity of storing UUIDs as strings at all? Once you start going into the rabbit hole, you better be prepared to go all the way down.
    - bitJericho 9y ago
      No doubt. You can store them as INTs! Tho it does get a little hairy dealing with the conversion with MySQL.
  - paulryanrogers 9y ago
    Is this true for all storage engines?
    - morgo 9y ago
      It is not true for innodb (default). See my other comments in this thread.
- sroussey 9y ago
  Storage, and index are different. You expand the UTF8 chars into bytes that are then part of a binary tree. It actually gets more interesting when you consider the collations.
eridius 9y ago
Really? That sounds like a tremendously stupid design. But even with that design, you'd expect VARCHAR(36) to have space for 36 ASCII characters, not 9.
- tveita 9y ago
  It does - it allows for 36 code points using your current charset, which with UTF-8 can mean up to 144 bytes. Of course, that is the worst case, but for index size limits, MySQL has to assume the worst case, which means you quickly bump into the default index key limit of 767 bytes with large string keys.
  - eridius 9y ago
    That can't be true. The article's anecdote is converting from LATIN-1 to UTF-8 causing the indexes to not be able to contain the "larger strings", but UUIDs are ASCII (when represented as strings), so the index size shouldn't change unless the storage engine is storing it internally as something other than UTF-8. And the parent comment says "In MySQL for example, using UTF-8 charset will cause it to use 3 or 4 bytes per character.", and if it's really using 3 or 4 bytes per character regardless of what that character is, then it's probably storing it internally as a sequence of unicode scalars instead of UTF-8. Granted, upon re-reading this, neither source explicitly says that a CHAR(36) column won't actually be able to hold 36 characters, but if it can then I don't know why the indexes would stop working. EDIT: It occurs to me that a CHAR(36) column would require 4 bytes per character to be allocated, since it's a fixed-width column, though it should still only actually use 1 byte for an ASCII character. But 144 bytes is still well under the 767 byte limit you mentioned.
    - zerocrates 9y ago
      It's true. The problem isn't so much the content itself, but the column and index definitions: keeping the same length in characters in the definition will use more bytes in utf8 or utf8mb4, at least against the storage engine index limits. I'm not sure whether this is because MySQL is just lazily/smartly computing the worst case, or if it's actually storing using multiple bytes per character regardless. I assume it's the former. The documentation's example of the problem with InnoDB goes like this: This definition is legal, a 255-character index at 3 bytes per char coming in under the 767-byte limit: col1 VARCHAR(500) CHARACTER SET utf8, INDEX (col1(255)) But to use utf8mb4 instead, which allows for characters beyond the BMP and can use up to 4 bytes per character, the index must be smaller, only covering the first 191 characters: col1 VARCHAR(500) CHARACTER SET utf8mb4, INDEX (col1(191)) It comes up often when people have used a VARCHAR(255) as a default "kinda big" string column, are indexing the whole thing, and want to switch to utf8mb4. In the case of the UUIDs in the article, I believe it mentioned it was a multiple-column index, so the increase of 3x (or 4x) from latin1 could easily push them over the limit if there's another big column or columns in the index.
sillysaurus3 9y ago
W... What? Why? The whole point of UTF-8 is that it's identical to Latin-1 (edit: ASCII) except in cases where it can't be. In those cases, it uses a very efficient encoding. I understand that people (for whatever reasons) don't want to store text in UTF-8, but that's incredible that such a popular piece of software would take that notion to the extreme. "Let's make UTF-8 almost equivalent to UTF-32 when the data is at rest! Yes!" Are you sure that info is correct? And if it's correct, are there any valid engineering reasons for that decision? It's probably best to be charitable and assume there must be a reason.
- sbov 9y ago
  It wouldn't be the only utf8 quirk on mysql: utf8 isn't even utf8. For that you need utf8mb4.
- mitchty 9y ago
  It is beyond correct. Mysql's "utf8" only stores stuff from 1-3 bytes. https://mathiasbynens.be/notes/mysql-utf8mb4 https://mathiasbynens.be/notes/mysql-utf8mb4 You have to use utf8mb4 to have proper utf8 support. Yes its stupid, this is the main reason I prefer not using mysql for anything. Too many gotchas lurking about.
  - nvivo 9y ago
    Exactly, but in any case even mysql version of UTF-8 should store an UUID the same way as ASCII.
    - seorphates 9y ago
      In dealing with conversions or characterset handling the UUID pitfall is the hyphen. This is an extremely vulnerable character when dealing with multiple data handling and feed sources (to say nothing of data conversions). The hyphen has a handful of imposters and one of the more troublesome ones is in extended ASCII as "en dash" (there's another as "em dash"). These do not have a direct mapping to unicode as they are not "hyphens" but they sure do look like them to humans in a hurry. It will throw your meticulously thought out and implemented keys (or any other well-measured column) into a tailspin. Dealing with extended ASCII implementations has been the most hazardous area of characterset handling, at least for me. Although probably unlikely the UUID is not a guaranteed direct ASCII to UNICODE conversion. I'd advise awareness and perhaps even some level of caution if you have a complex data flow (or, more apt, a seemingly ridiculously simple and bulletproof one). Extended ASCII sucks so if you use it and don't have one or both feet nailed to the floor then get off of it, sooner.
morgo 9y ago
Hi! I am the product manager for the mysql server. I can confirm that for innodb, this is not the case - storage is the variable length. You might be thinking about the memory storage engine (and temp tables).
- brainopener 9y ago
  I believe everyone is referencing the index key size length in MySQL. In that case you can get "ERROR 1071 (42000): Specified key was too long; max key length is xxxx bytes" errors. This happens even in InnoDB -- it's taking the maximum byte size of all the key fields. For a single VARCHAR(255) in MySQL utf8 that ends up being 3*255=765 for just that one field. If you add other fields, it's really easy to hit the max key length and have the create or alter fail.
  - morgo 9y ago
    See my reply to parent on why the max must be calculated (no false positives on duplicate key in edge case). The default row format changes in 5.7, so key size is max is now much larger.

by treve 9y ago

sp332 9y ago

Oh yeah, that makes sense. At least for CHAR(36), but I'm not sure how VARCHAR(36) as given in the article would behave. (Never mind I just looked it up and it depends on the storage engine - e.g. MEMORY will always allocate the max size.)

bitJericho 9y ago
You'd be insane to put the primary key in a varchar. Mysql is much faster with static width tables. Your main user table should be static width for all the fields.
- cwyers 9y ago
  As opposed to the insanity of storing UUIDs as strings at all? Once you start going into the rabbit hole, you better be prepared to go all the way down.
  - bitJericho 9y ago
    No doubt. You can store them as INTs! Tho it does get a little hairy dealing with the conversion with MySQL.
- paulryanrogers 9y ago
  Is this true for all storage engines?
  - morgo 9y ago
    It is not true for innodb (default). See my other comments in this thread.
sroussey 9y ago
Storage, and index are different. You expand the UTF8 chars into bytes that are then part of a binary tree. It actually gets more interesting when you consider the collations.

eridius 9y ago

Really? That sounds like a tremendously stupid design. But even with that design, you'd expect VARCHAR(36) to have space for 36 ASCII characters, not 9.

tveita 9y ago
It does - it allows for 36 code points using your current charset, which with UTF-8 can mean up to 144 bytes. Of course, that is the worst case, but for index size limits, MySQL has to assume the worst case, which means you quickly bump into the default index key limit of 767 bytes with large string keys.
- eridius 9y ago
  That can't be true. The article's anecdote is converting from LATIN-1 to UTF-8 causing the indexes to not be able to contain the "larger strings", but UUIDs are ASCII (when represented as strings), so the index size shouldn't change unless the storage engine is storing it internally as something other than UTF-8. And the parent comment says "In MySQL for example, using UTF-8 charset will cause it to use 3 or 4 bytes per character.", and if it's really using 3 or 4 bytes per character regardless of what that character is, then it's probably storing it internally as a sequence of unicode scalars instead of UTF-8. Granted, upon re-reading this, neither source explicitly says that a CHAR(36) column won't actually be able to hold 36 characters, but if it can then I don't know why the indexes would stop working. EDIT: It occurs to me that a CHAR(36) column would require 4 bytes per character to be allocated, since it's a fixed-width column, though it should still only actually use 1 byte for an ASCII character. But 144 bytes is still well under the 767 byte limit you mentioned.
  - zerocrates 9y ago
    It's true. The problem isn't so much the content itself, but the column and index definitions: keeping the same length in characters in the definition will use more bytes in utf8 or utf8mb4, at least against the storage engine index limits. I'm not sure whether this is because MySQL is just lazily/smartly computing the worst case, or if it's actually storing using multiple bytes per character regardless. I assume it's the former. The documentation's example of the problem with InnoDB goes like this: This definition is legal, a 255-character index at 3 bytes per char coming in under the 767-byte limit: col1 VARCHAR(500) CHARACTER SET utf8, INDEX (col1(255)) But to use utf8mb4 instead, which allows for characters beyond the BMP and can use up to 4 bytes per character, the index must be smaller, only covering the first 191 characters: col1 VARCHAR(500) CHARACTER SET utf8mb4, INDEX (col1(191)) It comes up often when people have used a VARCHAR(255) as a default "kinda big" string column, are indexing the whole thing, and want to switch to utf8mb4. In the case of the UUIDs in the article, I believe it mentioned it was a multiple-column index, so the increase of 3x (or 4x) from latin1 could easily push them over the limit if there's another big column or columns in the index.

sillysaurus3 9y ago

W... What? Why? The whole point of UTF-8 is that it's identical to Latin-1 (edit: ASCII) except in cases where it can't be. In those cases, it uses a very efficient encoding. I understand that people (for whatever reasons) don't want to store text in UTF-8, but that's incredible that such a popular piece of software would take that notion to the extreme. "Let's make UTF-8 almost equivalent to UTF-32 when the data is at rest! Yes!" Are you sure that info is correct? And if it's correct, are there any valid engineering reasons for that decision? It's probably best to be charitable and assume there must be a reason.

sbov 9y ago
It wouldn't be the only utf8 quirk on mysql: utf8 isn't even utf8. For that you need utf8mb4.
mitchty 9y ago
It is beyond correct. Mysql's "utf8" only stores stuff from 1-3 bytes. https://mathiasbynens.be/notes/mysql-utf8mb4 https://mathiasbynens.be/notes/mysql-utf8mb4 You have to use utf8mb4 to have proper utf8 support. Yes its stupid, this is the main reason I prefer not using mysql for anything. Too many gotchas lurking about.
- nvivo 9y ago
  Exactly, but in any case even mysql version of UTF-8 should store an UUID the same way as ASCII.
  - seorphates 9y ago
    In dealing with conversions or characterset handling the UUID pitfall is the hyphen. This is an extremely vulnerable character when dealing with multiple data handling and feed sources (to say nothing of data conversions). The hyphen has a handful of imposters and one of the more troublesome ones is in extended ASCII as "en dash" (there's another as "em dash"). These do not have a direct mapping to unicode as they are not "hyphens" but they sure do look like them to humans in a hurry. It will throw your meticulously thought out and implemented keys (or any other well-measured column) into a tailspin. Dealing with extended ASCII implementations has been the most hazardous area of characterset handling, at least for me. Although probably unlikely the UUID is not a guaranteed direct ASCII to UNICODE conversion. I'd advise awareness and perhaps even some level of caution if you have a complex data flow (or, more apt, a seemingly ridiculously simple and bulletproof one). Extended ASCII sucks so if you use it and don't have one or both feet nailed to the floor then get off of it, sooner.

morgo 9y ago

Hi! I am the product manager for the mysql server. I can confirm that for innodb, this is not the case - storage is the variable length. You might be thinking about the memory storage engine (and temp tables).

brainopener 9y ago
I believe everyone is referencing the index key size length in MySQL. In that case you can get "ERROR 1071 (42000): Specified key was too long; max key length is xxxx bytes" errors. This happens even in InnoDB -- it's taking the maximum byte size of all the key fields. For a single VARCHAR(255) in MySQL utf8 that ends up being 3*255=765 for just that one field. If you add other fields, it's really easy to hit the max key length and have the create or alter fail.
- morgo 9y ago
  See my reply to parent on why the max must be calculated (no false positives on duplicate key in edge case). The default row format changes in 5.7, so key size is max is now much larger.