If I only use only ASCII characters, will VARCHAR (255) with utf8mb4_0900_ai_ci be larger on disk than VARCHAR (255) using ASCII?
-
This appears to be a minefield (+1 for a thought-provoking question) - see my comment on the accepted answer also.– VéraceCommented Jun 12, 2020 at 9:03
-
@Vérace and jsHate: no, not really a minefield, at least not as implied. Encodings in general can be a minefield, but what you found is a problem with that site. It could be an issue converting incoming bytes into the app logic, or translating between app layer and DB. Could be a driver configuration setting problem since MySQL does let you set connection collation separate from column collation. Please see my reply for links with examples: dba.stackexchange.com/questions/269014/…– Solomon RutzkyCommented Jun 12, 2020 at 19:59
Add a comment
|
2 Answers
The Fiddle is wrong.
あ A い I う U え E お O.
is 20 characters / 40 bytes when declaring that the client is encoded in utf8 (or utf8mb4). But if you claim that that it is in latin1, it leads to Mojibake or "double-encoding", hence the 30 and 48 that Fiddle shows.
あ A い I う U え E お O. --> E38182 41 E38184 49 E38186 55 E38188 45 E3818A 4F 2E
For further discussion of what went wrong, see "double encoding" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored . I don't have the source code to "fix" Fiddle.
That is, E38182 is the 3 hex bytes for the HIRAGANA LETTER A: あ
But, if you treat E38182 (etc) as latin1, it shows as ゠A ㄠI ㆠU ㈠E ㊠O.. Then if you convert again to utf8, you get
C3A3 C281 E2809A 20 41 20 C3A3 C281 E2809E 20 49 20 ...
You can still recognize the spaces (20), A (41), I (49), etc, but the Hiragana characters have been mangled.
You don't see the double-encoding in Fiddle because the browser is 'kind enough' to 'fix' your mistake. (This makes figuring out what went wrong quite devilish.)
The Chinese hex is E683B3 E79C8B E4BB80 E9A0AD E6B885 E58FAA E582B7 E7B2BE EFBC8C E4B8AD E7BE8E E8A780 E79A84 E68EA5 E5A794 E4B8BB E58091 E8AA8D E58FAF E69893 E795AB E7AD89 E58AA9 E6B5B7 E59BA0 09
(The tab (09) at the end may be an artifict of the formatting.)
The double encoding starts with C3A6 C692 C2B3 (from EF, BC, 8C) C3A7 C593 E280B9 C3A4 C2BB E282AC C3A9 C2A0 C2AD C3A6 C2B8 E280A6
Back to the Title Question -- There are minor subtle differences, even when all you use is ascii.
You will probably not encounter any measurable difference. Here are som possibilities.
Certain temp table actions may hit limits sooner. (This problem existed in 5.7, but may have been more than eliminated in 8.0 by now turning
VARCHARintoCHARwhen building temp tables.)I have yet to see a benchmark that shows that utf8mb4 collations of ascii text are or are not as fast as
CHARACTER SETlatin1 or ascii.Index limits are shorter for CHARSET utf8mb4 than for CHARSET ascii. They are probably
VARCHAR(3072)versusVARCHAR(768).
answered Jun 13, 2020 at 4:03
-
Nice post and thanks for the effort you obviously put into it! (+1). I'm puzzled by this line
You don't see the double-encoding in Fiddle because the browser is 'kind enough' to 'fix' your mistake. (The makes figuring out what went wrong quite devilish.). How do you know this? Did you use a *nix command line tool to interrogate the site? I think my remark about this being a "minefield" holds true... Although, as pointed out by @SolomonRutzky, the SQL Server instance behaves as one would expect and one that makes sense - to my mind anyway!– VéraceCommented Jun 13, 2020 at 5:53 -
@Vérace It's not so much that the browser "fixes" anything, it's that the encoding between the browser and the app is consistently UTF-8, while the encoding between the app and MySQL is consistently Latin1. So, on the way in, it's: UTF-8 -> Latin1 -> UTF-8 (column). On the way out, it's: UTF-8 (column) -> Latin1 -> UTF-8. In a sense the data gets encoded on the way in, and decoded on the way out, so it looks correct when selected, but using the
HEX()function we can see the "encoded" bytes stored in the column (see first link in my reply comment to your comment on my answer). Commented Jun 13, 2020 at 7:58 -
@Vérace Also, I figured out the problem and posted an answer to your question on TopAnswers. Adding
SET NAMES 'utf8mb4';at the beginning of a MySQL db<>fiddle will correct the issue. Not a good long term solution (they need to fix it in the config file), but it does fix it regardless of whether or not the site fixes the underlying issue. Commented Jun 13, 2020 at 8:05 -
@Vérace (and Solomon) - MySQL needs the charset specified in 4 or 5 places. Hence, the existence of about 5 symptoms. The 48 and 30 (lengths in the Fiddle) was the biggest clue. I ran the string through php code to create the double-encoding and came up with 48 and 30. So I concluded (OK, "jumped to the conclusion") that it was double-encoded. I first screwed up more than a decade ago (in MySQL 4.1); I have been determined to atone for my screwup. Commented Jun 13, 2020 at 17:26
-
@SolomonRutzky - Caution... If a user is deliberately doing something in latin1, will Fiddle screw up in the 'opposite' way? Also, pre-5.5, utf8mb4 was not available. (PS, I appreciate the existence of Fiddle.) Commented Jun 13, 2020 at 17:28
Unless MySQL does something strange, using only ASCII characters (i.e. only values 0 - 127) should be the exact same encoding, and hence the exact same size, between ASCII, UTF-8, and many other 8-bit code pages. It's only when you hit code points above 127 (or 0x7F) that UTF-8 starts to require additional space (though technically speaking, standard ASCII only includes values 0 - 127, thus there are no code points above 127, thus all ASCII code points are encoded identically in UTF-8, which after all, was the design goal of UTF-8: full ASCII compatibility).
answered Jun 12, 2020 at 7:52
-
-
-
One thing to take into consideration is that utf8mb4 indexes will require 4x the size than ASCII indexes. That is, a MyISAM ASCII column can take up to 1000 byes, leading to situations where the longest utf8mb4 index is 250 characters long. Anything above 1000 bytes will generate an error. InnoDB used to have a limit of 767 bytes, but there were workarounds for it that don't seem needed anymore. I couldn't find the max index size for InnoDB besides this information. Commented Jun 12, 2020 at 17:53
-
@Vérace the values shown for
LENGTHandCHAR_LENGTHare incorrect due to an encoding bug with the dbfiddle site. I don't have time to investigate to find the exact location, but I adapted your query to show the issue more clearly: dbfiddle.uk/… . I also created a SQL Server version of it that shows everything working as expected, using SQL Server 2019 in order to get UTF-8: dbfiddle.uk/… Commented Jun 12, 2020 at 19:53 -
1@SolomonRutzky Thanks for going to the trouble of doing that - the SQL Server numbers I get totally - really clears things up for me!– VéraceCommented Jun 12, 2020 at 20:23