4

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érace Jun 12 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 Rutzky Jun 12 at 19:59
1

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 VARCHAR into CHAR when 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 SET latin1 or ascii.

  • Index limits are shorter for CHARSET utf8mb4 than for CHARSET ascii. They are probably VARCHAR(3072) versus VARCHAR(768).

| improve this answer | |
  • 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érace Jun 13 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). – Solomon Rutzky Jun 13 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. – Solomon Rutzky Jun 13 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. – Rick James Jun 13 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.) – Rick James Jun 13 at 17:28
5

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).

| improve this answer | |
  • I was looking at this and came across this - care to comment? Japanese string has (AFAICS) 20 characters and the Chinese string has 25 - why the discrepancies? (+1 BTW). – Vérace Jun 12 at 9:02
  • and don*'t optimize the table or else you double the row size, – nbk Jun 12 at 9:46
  • 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. – Ismael Miguel Jun 12 at 17:53
  • @Vérace the values shown for LENGTH and CHAR_LENGTH are 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/… – Solomon Rutzky Jun 12 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érace Jun 12 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.