4 ms·

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 effi

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.
  - sroussey 9y ago
    Store, yes. But indexes don't do variable length (think about the index being a btree of binary values), so it gets expanded. UTF8 (utf8mb4) will thus expand to 4 bytes per character. And MySQL (pre 5.7) will stop you from making such stupid indices.
TazeTSchnitzel 9y ago
> The whole point of UTF-8 is that it's identical to Latin-1 except in cases where it can't be. It's only compatible with ASCII, not Latin-1. You are maybe confusing UTF-8 with the Unicode codepoint space, of which the first 256 codepoints are the same as Latin-1.
- sillysaurus3 9y ago
  Aha, thanks for calling me out. I was indeed thinking of ASCII.
- dlubarov 9y ago
  Just in case there's any confusion: - 7-bit ASCII encodes 128 characters; latin-1 encodes 256 characters; UTF-8 encodes all unicode characters - latin-1 and UTF-8 both extend 7-bit ASCII; the three encodings are equivalent for ordinals 0-127 - UUID characters are part of 7-bit ASCII, so they have the same encoding in ASCII, latin-1 and UTF-8
  - seorphates 9y ago
    100% correct - UUID strings are ASCII < 128 .. but there are imposters for character code 45 ("-"). Assumption based approaches to conversions or field handling should not be assumed to be safe ones just because UUID is "technically" ASCII. Only the keeper of the field can truly enforce that constraint.
geofft 9y ago
I think this is correct for CHAR and incorrect for VARCHAR. A CHAR column always allocates the maximum size, so a utf8 (MySQL's weird "UTF-8, but only for the BMP" encoding) CHAR(100) needs to reserve 300 bytes per row, and a utf8mb4 (MySQL's actual UTF-8 encoding) CHAR(100) needs to reserve 400 bytes per row. But a VARCHAR is dynamically allocated, so, yes, a VARCHAR(100) that stores 100 lower-ASCII characters is only going to use 102 bytes of storage (using 2 bytes for the string length). See, e.g., the "tip" section at the end of https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8.html https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8...
- deleted 9y ago
  [deleted]
- morgo 9y ago
  With innodb it is variable storage regardless of varchar or char when UTF8.
- ivanhoe 9y ago
  Author mentions compound keys, so issue was probably with the index key prefix length, which is by default limited at 767 bytes. That limits you to a maximum of having just first 255 characters of string data indexed with utf8, but in latin1 (one byte per character) you can have 767 characters long prefix. So with latin1 it's perfectly ok to have compound index on 2 varchar(255) columns, but you can't do that with utf8. Converting from latin1 to utf8 will break all those indices.
  - morgo 9y ago
    The 767 limit is with the default row format. In 5.7 the row format changes, and allows 3000+
devrandomguy 9y ago
(In David Attenboroughs voice): And here we have the gentle Sillysaurus in it's natural environment, grazing on a field of failweed in the tidal shallows. Notice however, that his eyes remain just above the surface of the water, ever vigilant for the dreaded Snarkasaurus Rex. A flock of Correctosaurs glide by overhead, scanning the muck with uncanny vision for the telltale motion of their Suttlebug prey. It is their rare, mid-air collisions that give the Suttlebug away, despite their otherwise excellent camouflage.

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.
- sroussey 9y ago
  Store, yes. But indexes don't do variable length (think about the index being a btree of binary values), so it gets expanded. UTF8 (utf8mb4) will thus expand to 4 bytes per character. And MySQL (pre 5.7) will stop you from making such stupid indices.

TazeTSchnitzel 9y ago

> The whole point of UTF-8 is that it's identical to Latin-1 except in cases where it can't be. It's only compatible with ASCII, not Latin-1. You are maybe confusing UTF-8 with the Unicode codepoint space, of which the first 256 codepoints are the same as Latin-1.

sillysaurus3 9y ago
Aha, thanks for calling me out. I was indeed thinking of ASCII.
dlubarov 9y ago
Just in case there's any confusion: - 7-bit ASCII encodes 128 characters; latin-1 encodes 256 characters; UTF-8 encodes all unicode characters - latin-1 and UTF-8 both extend 7-bit ASCII; the three encodings are equivalent for ordinals 0-127 - UUID characters are part of 7-bit ASCII, so they have the same encoding in ASCII, latin-1 and UTF-8
- seorphates 9y ago
  100% correct - UUID strings are ASCII < 128 .. but there are imposters for character code 45 ("-"). Assumption based approaches to conversions or field handling should not be assumed to be safe ones just because UUID is "technically" ASCII. Only the keeper of the field can truly enforce that constraint.

geofft 9y ago

I think this is correct for CHAR and incorrect for VARCHAR. A CHAR column always allocates the maximum size, so a utf8 (MySQL's weird "UTF-8, but only for the BMP" encoding) CHAR(100) needs to reserve 300 bytes per row, and a utf8mb4 (MySQL's actual UTF-8 encoding) CHAR(100) needs to reserve 400 bytes per row. But a VARCHAR is dynamically allocated, so, yes, a VARCHAR(100) that stores 100 lower-ASCII characters is only going to use 102 bytes of storage (using 2 bytes for the string length). See, e.g., the "tip" section at the end of https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8.html https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8...

deleted 9y ago
[deleted]
morgo 9y ago
With innodb it is variable storage regardless of varchar or char when UTF8.
ivanhoe 9y ago
Author mentions compound keys, so issue was probably with the index key prefix length, which is by default limited at 767 bytes. That limits you to a maximum of having just first 255 characters of string data indexed with utf8, but in latin1 (one byte per character) you can have 767 characters long prefix. So with latin1 it's perfectly ok to have compound index on 2 varchar(255) columns, but you can't do that with utf8. Converting from latin1 to utf8 will break all those indices.
- morgo 9y ago
  The 767 limit is with the default row format. In 5.7 the row format changes, and allows 3000+

devrandomguy 9y ago

(In David Attenboroughs voice): And here we have the gentle Sillysaurus in it's natural environment, grazing on a field of failweed in the tidal shallows. Notice however, that his eyes remain just above the surface of the water, ever vigilant for the dreaded Snarkasaurus Rex. A flock of Correctosaurs glide by overhead, scanning the muck with uncanny vision for the telltale motion of their Suttlebug prey. It is their rare, mid-air collisions that give the Suttlebug away, despite their otherwise excellent camouflage.