0

I am trying to fix some corruption to character fields in my database after a migration. I suspect that part of the problem is that fields with UTF-8 data were interpreted with the MySQL latin1 character set. Before I try to fix the problem, I want to understand it. Part of that is to understand exactly what the latin1 character set is.

What is the formal definition of mapping between code points (byte values) and characters for the latin1 character set in MySQL 8.0, and in MySQL 5.7? If collation matters, I will take the answer for latin1_swedish_ci collation.

latin1 is said to be identical to the Windows CP-1252 encoding. In the Wikipedia article on CP-1252, there is a table of 256 byte code values, with a character in each cell of the table. That is the sort of definition I am after. But, I am hoping for something more rigorous than "it's the same as CP-1252" and "that table in Wikipedia is probably accurate".

I have tried looking through the MySQL documentation. I hoped that 10.10.2 West European Character Sets would like to the formal definition, but I do not see it there. Nor do I see an answer here, or on Wikipedia.

If there is a MySQL source file which defines latin1, and I can view that source file via a URL, that might well be good enough. But if someone has made up a 256-cell table, that is even better.

3
  • There is a comment in the MySQL source code explaining their encoding here: github.com/mysql/mysql-server/blob/8.0/strings/… and arrays which I infer map the code points to other encodings, but I don't understand how they are used. Sep 1, 2022 at 15:32
  • @ErgestBasha Thank you for the link. It might be helpful for a question about, "how do I fix the corruption?", but this question is "what is the definition of latin1?". Sep 1, 2022 at 19:42
  • @BillKarwin Congratulations! The link you give to the MySQL source appears to be the core of a definitive answer, because it shows exactly how MySQL interprets Latin1 code units (byte values) in terms of Unicode characters. Make it an answer, and I'll upvote it. Add in a description of how to interpret the code, and I'll accept it as the correct answer. Good work! Sep 1, 2022 at 19:46

2 Answers 2

1

If you don't correctly tell MySQL the encoding of the client, various forms of gibberish -- Mojibake, truncation, question marks, etc -- can occur. For help in that area, see https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored For "corruption", I recommend starting with that.

Will this table here suffice for seeing the mapping between latin1 and UTF-8; it's the top 128 of your "256-cell table"? http://mysql.rjweb.org/doc.php/charcoll#8_bit_encodings It has columns for decimal, latin1 hex, utf8 hex, the printable character, htmlentity. Search the web for "ascii"; there are a zillion pages that show the bottom 28.

It is not a formal definition; I sometimes use it as a crutch in answering questions on this forum.

URLs should be encoded with PHP's urlencode() (or equivalent), which turns 8-bit codes into 3 characters: '%' plus two hex chars.

As for latinx versus CPxxxx, there seem to be a very few characters that do not exactly match; usually in the 8x or 9x range. "Its the same" has been good enough, since the main differences (eg 216 D8 C398 Ø Ø) is quite glaring.

UTF-8 was being invented before the Euro became a standard currency. Hence "80" used to be (and may still be) a point of difference: 128 80 E282AC € € Note the 3-byte UTF-8 encoding.

I often run into Microsoft Word documents spitting out an odd space 160 A0 C2A0  . Hex 20 is an ordinary space. Turn on the high bit and you get A0, which seems to equate to HTML's   (non-breaking space). A UTF-8 parser spits at a plain A0, but is happy with its equivalent, C2A0.

1
  • Upvoted for the link to the chart of the top 128 characters. It looks like a helpful crutch. Thank you! I agree that the bottom 128 characters are the same as ASCII and charts for them abound. In fact, the Wikipedia article on CP-1252 en.wikipedia.org/wiki/Windows-1252 is as good a chart of the bottom 128 as any. I don't fully agree with other details in your answer, but they are off the topic of my question about MySQL 'latin1' anyway. Sep 1, 2022 at 19:40
0

If you can't find a specification, one way to answer the question is to test the system. The following query generates code units (byte values) from 00 to FF, interprets them as characters using the latin1 encoding, then delivers a table of the code unit, the character, the Unicode scalar value (which one can look up in The Unicode Standard's character code charts), and the UTF-8 code units (bytes):

with 
recursive code_units(code) as (
    select 0 as 'code'
    union all 
    select code+1 from code_units where code < 255
),
latin1_repertoire(code, char_latin1, unicode_scalar, utf8mb4_code_units) as (
    select 
        code, 
        char(code USING 'latin1'), 
        convert(char(code USING 'latin1') using 'utf32'), 
        convert(char(code USING 'latin1') using 'utf8mb4')
    from code_units
)
select
    hex(char_latin1) as 'latin1 hex', 
    concat('U-',right(hex(unicode_scalar),6)) as 'Unicode scalar',
    hex(utf8mb4_code_units) as 'utf-8 hex',
    char_latin1 as 'latin1 char' 
from latin1_repertoire;

Here is what I get when I run this query on my copy of MySQL 8.0.30:

+------------+----------------+-----------+-------------+
| latin1 hex | Unicode scalar | utf-8 hex | latin1 char |
+------------+----------------+-----------+-------------+
| 00         | U-000000       | 00        |             |
| 01         | U-000001       | 01        |            |
| 02         | U-000002       | 02        |            |
| 03         | U-000003       | 03        |            |
| 04         | U-000004       | 04        |            |
| 05         | U-000005       | 05        |            |
| 06         | U-000006       | 06        |            |
| 07         | U-000007       | 07        |            |
| 08         | U-000008       | 08        |           |
| 09         | U-000009       | 09        |                |
| 0A         | U-00000A       | 0A        | 
           |
| 0B         | U-00000B       | 0B        | 
                                                       |
| 0C         | U-00000C       | 0C        | 
                                                       |
           | | U-00000D       | 0D        | 
| 0E         | U-00000E       | 0E        |            |
| 0F         | U-00000F       | 0F        |            |
| 10         | U-000010       | 10        |            |
| 11         | U-000011       | 11        |            |
| 12         | U-000012       | 12        |            |
| 13         | U-000013       | 13        |            |
| 14         | U-000014       | 14        |            |
| 15         | U-000015       | 15        |            |
| 16         | U-000016       | 16        |            |
| 17         | U-000017       | 17        |            |
| 18         | U-000018       | 18        |            |
| 19         | U-000019       | 19        |            |
| 1A         | U-00001A       | 1A        |            |
| 1B         | U-00001B       | 1B        |           |
| 1C         | U-00001C       | 1C        |            |
| 1D         | U-00001D       | 1D        |            |
| 1E         | U-00001E       | 1E        |            |
| 1F         | U-00001F       | 1F        |            |
| 20         | U-000020       | 20        |             |
| 21         | U-000021       | 21        | !           |
| 22         | U-000022       | 22        | "           |
| 23         | U-000023       | 23        | #           |
| 24         | U-000024       | 24        | $           |
| 25         | U-000025       | 25        | %           |
| 26         | U-000026       | 26        | &           |
| 27         | U-000027       | 27        | '           |
| 28         | U-000028       | 28        | (           |
| 29         | U-000029       | 29        | )           |
| 2A         | U-00002A       | 2A        | *           |
| 2B         | U-00002B       | 2B        | +           |
| 2C         | U-00002C       | 2C        | ,           |
| 2D         | U-00002D       | 2D        | -           |
| 2E         | U-00002E       | 2E        | .           |
| 2F         | U-00002F       | 2F        | /           |
| 30         | U-000030       | 30        | 0           |
| 31         | U-000031       | 31        | 1           |
| 32         | U-000032       | 32        | 2           |
| 33         | U-000033       | 33        | 3           |
| 34         | U-000034       | 34        | 4           |
| 35         | U-000035       | 35        | 5           |
| 36         | U-000036       | 36        | 6           |
| 37         | U-000037       | 37        | 7           |
| 38         | U-000038       | 38        | 8           |
| 39         | U-000039       | 39        | 9           |
| 3A         | U-00003A       | 3A        | :           |
| 3B         | U-00003B       | 3B        | ;           |
| 3C         | U-00003C       | 3C        | <           |
| 3D         | U-00003D       | 3D        | =           |
| 3E         | U-00003E       | 3E        | >           |
| 3F         | U-00003F       | 3F        | ?           |
| 40         | U-000040       | 40        | @           |
| 41         | U-000041       | 41        | A           |
| 42         | U-000042       | 42        | B           |
| 43         | U-000043       | 43        | C           |
| 44         | U-000044       | 44        | D           |
| 45         | U-000045       | 45        | E           |
| 46         | U-000046       | 46        | F           |
| 47         | U-000047       | 47        | G           |
| 48         | U-000048       | 48        | H           |
| 49         | U-000049       | 49        | I           |
| 4A         | U-00004A       | 4A        | J           |
| 4B         | U-00004B       | 4B        | K           |
| 4C         | U-00004C       | 4C        | L           |
| 4D         | U-00004D       | 4D        | M           |
| 4E         | U-00004E       | 4E        | N           |
| 4F         | U-00004F       | 4F        | O           |
| 50         | U-000050       | 50        | P           |
| 51         | U-000051       | 51        | Q           |
| 52         | U-000052       | 52        | R           |
| 53         | U-000053       | 53        | S           |
| 54         | U-000054       | 54        | T           |
| 55         | U-000055       | 55        | U           |
| 56         | U-000056       | 56        | V           |
| 57         | U-000057       | 57        | W           |
| 58         | U-000058       | 58        | X           |
| 59         | U-000059       | 59        | Y           |
| 5A         | U-00005A       | 5A        | Z           |
| 5B         | U-00005B       | 5B        | [           |
| 5C         | U-00005C       | 5C        | \           |
| 5D         | U-00005D       | 5D        | ]           |
| 5E         | U-00005E       | 5E        | ^           |
| 5F         | U-00005F       | 5F        | _           |
| 60         | U-000060       | 60        | `           |
| 61         | U-000061       | 61        | a           |
| 62         | U-000062       | 62        | b           |
| 63         | U-000063       | 63        | c           |
| 64         | U-000064       | 64        | d           |
| 65         | U-000065       | 65        | e           |
| 66         | U-000066       | 66        | f           |
| 67         | U-000067       | 67        | g           |
| 68         | U-000068       | 68        | h           |
| 69         | U-000069       | 69        | i           |
| 6A         | U-00006A       | 6A        | j           |
| 6B         | U-00006B       | 6B        | k           |
| 6C         | U-00006C       | 6C        | l           |
| 6D         | U-00006D       | 6D        | m           |
| 6E         | U-00006E       | 6E        | n           |
| 6F         | U-00006F       | 6F        | o           |
| 70         | U-000070       | 70        | p           |
| 71         | U-000071       | 71        | q           |
| 72         | U-000072       | 72        | r           |
| 73         | U-000073       | 73        | s           |
| 74         | U-000074       | 74        | t           |
| 75         | U-000075       | 75        | u           |
| 76         | U-000076       | 76        | v           |
| 77         | U-000077       | 77        | w           |
| 78         | U-000078       | 78        | x           |
| 79         | U-000079       | 79        | y           |
| 7A         | U-00007A       | 7A        | z           |
| 7B         | U-00007B       | 7B        | {           |
| 7C         | U-00007C       | 7C        | |           |
| 7D         | U-00007D       | 7D        | }           |
| 7E         | U-00007E       | 7E        | ~           |
| 7F         | U-00007F       | 7F        |            |
| 80         | U-0020AC       | E282AC    | €           |
| 81         | U-000081       | C281      |            |
| 82         | U-00201A       | E2809A    | ‚           |
| 83         | U-000192       | C692      | ƒ           |
| 84         | U-00201E       | E2809E    | „           |
| 85         | U-002026       | E280A6    | …           |
| 86         | U-002020       | E280A0    | †           |
| 87         | U-002021       | E280A1    | ‡           |
| 88         | U-0002C6       | CB86      | ˆ           |
| 89         | U-002030       | E280B0    | ‰           |
| 8A         | U-000160       | C5A0      | Š           |
| 8B         | U-002039       | E280B9    | ‹           |
| 8C         | U-000152       | C592      | Π          |
| 8D         | U-00008D       | C28D      |            |
| 8E         | U-00017D       | C5BD      | Ž           |
| 8F         | U-00008F       | C28F      |            |
| 90         | U-000090       | C290      |            |
| 91         | U-002018       | E28098    | ‘           |
| 92         | U-002019       | E28099    | ’           |
| 93         | U-00201C       | E2809C    | “           |
| 94         | U-00201D       | E2809D    | ”           |
| 95         | U-002022       | E280A2    | •           |
| 96         | U-002013       | E28093    | –           |
| 97         | U-002014       | E28094    | —           |
| 98         | U-0002DC       | CB9C      | ˜           |
| 99         | U-002122       | E284A2    | ™           |
| 9A         | U-000161       | C5A1      | š           |
| 9B         | U-00203A       | E280BA    | ›           |
| 9C         | U-000153       | C593      | œ           |
| 9D         | U-00009D       | C29D      |            |
| 9E         | U-00017E       | C5BE      | ž           |
| 9F         | U-000178       | C5B8      | Ÿ           |
| A0         | U-0000A0       | C2A0      |             |
| A1         | U-0000A1       | C2A1      | ¡           |
| A2         | U-0000A2       | C2A2      | ¢           |
| A3         | U-0000A3       | C2A3      | £           |
| A4         | U-0000A4       | C2A4      | ¤           |
| A5         | U-0000A5       | C2A5      | ¥           |
| A6         | U-0000A6       | C2A6      | ¦           |
| A7         | U-0000A7       | C2A7      | §           |
| A8         | U-0000A8       | C2A8      | ¨           |
| A9         | U-0000A9       | C2A9      | ©           |
| AA         | U-0000AA       | C2AA      | ª           |
| AB         | U-0000AB       | C2AB      | «           |
| AC         | U-0000AC       | C2AC      | ¬           |
| AD         | U-0000AD       | C2AD      | ­           |
| AE         | U-0000AE       | C2AE      | ®           |
| AF         | U-0000AF       | C2AF      | ¯           |
| B0         | U-0000B0       | C2B0      | °           |
| B1         | U-0000B1       | C2B1      | ±           |
| B2         | U-0000B2       | C2B2      | ²           |
| B3         | U-0000B3       | C2B3      | ³           |
| B4         | U-0000B4       | C2B4      | ´           |
| B5         | U-0000B5       | C2B5      | µ           |
| B6         | U-0000B6       | C2B6      | ¶           |
| B7         | U-0000B7       | C2B7      | ·           |
| B8         | U-0000B8       | C2B8      | ¸           |
| B9         | U-0000B9       | C2B9      | ¹           |
| BA         | U-0000BA       | C2BA      | º           |
| BB         | U-0000BB       | C2BB      | »           |
| BC         | U-0000BC       | C2BC      | ¼           |
| BD         | U-0000BD       | C2BD      | ½           |
| BE         | U-0000BE       | C2BE      | ¾           |
| BF         | U-0000BF       | C2BF      | ¿           |
| C0         | U-0000C0       | C380      | À           |
| C1         | U-0000C1       | C381      | Á           |
| C2         | U-0000C2       | C382      | Â           |
| C3         | U-0000C3       | C383      | Ã           |
| C4         | U-0000C4       | C384      | Ä           |
| C5         | U-0000C5       | C385      | Å           |
| C6         | U-0000C6       | C386      | Æ           |
| C7         | U-0000C7       | C387      | Ç           |
| C8         | U-0000C8       | C388      | È           |
| C9         | U-0000C9       | C389      | É           |
| CA         | U-0000CA       | C38A      | Ê           |
| CB         | U-0000CB       | C38B      | Ë           |
| CC         | U-0000CC       | C38C      | Ì           |
| CD         | U-0000CD       | C38D      | Í           |
| CE         | U-0000CE       | C38E      | Î           |
| CF         | U-0000CF       | C38F      | Ï           |
| D0         | U-0000D0       | C390      | Ð           |
| D1         | U-0000D1       | C391      | Ñ           |
| D2         | U-0000D2       | C392      | Ò           |
| D3         | U-0000D3       | C393      | Ó           |
| D4         | U-0000D4       | C394      | Ô           |
| D5         | U-0000D5       | C395      | Õ           |
| D6         | U-0000D6       | C396      | Ö           |
| D7         | U-0000D7       | C397      | ×           |
| D8         | U-0000D8       | C398      | Ø           |
| D9         | U-0000D9       | C399      | Ù           |
| DA         | U-0000DA       | C39A      | Ú           |
| DB         | U-0000DB       | C39B      | Û           |
| DC         | U-0000DC       | C39C      | Ü           |
| DD         | U-0000DD       | C39D      | Ý           |
| DE         | U-0000DE       | C39E      | Þ           |
| DF         | U-0000DF       | C39F      | ß           |
| E0         | U-0000E0       | C3A0      | à           |
| E1         | U-0000E1       | C3A1      | á           |
| E2         | U-0000E2       | C3A2      | â           |
| E3         | U-0000E3       | C3A3      | ã           |
| E4         | U-0000E4       | C3A4      | ä           |
| E5         | U-0000E5       | C3A5      | å           |
| E6         | U-0000E6       | C3A6      | æ           |
| E7         | U-0000E7       | C3A7      | ç           |
| E8         | U-0000E8       | C3A8      | è           |
| E9         | U-0000E9       | C3A9      | é           |
| EA         | U-0000EA       | C3AA      | ê           |
| EB         | U-0000EB       | C3AB      | ë           |
| EC         | U-0000EC       | C3AC      | ì           |
| ED         | U-0000ED       | C3AD      | í           |
| EE         | U-0000EE       | C3AE      | î           |
| EF         | U-0000EF       | C3AF      | ï           |
| F0         | U-0000F0       | C3B0      | ð           |
| F1         | U-0000F1       | C3B1      | ñ           |
| F2         | U-0000F2       | C3B2      | ò           |
| F3         | U-0000F3       | C3B3      | ó           |
| F4         | U-0000F4       | C3B4      | ô           |
| F5         | U-0000F5       | C3B5      | õ           |
| F6         | U-0000F6       | C3B6      | ö           |
| F7         | U-0000F7       | C3B7      | ÷           |
| F8         | U-0000F8       | C3B8      | ø           |
| F9         | U-0000F9       | C3B9      | ù           |
| FA         | U-0000FA       | C3BA      | ú           |
| FB         | U-0000FB       | C3BB      | û           |
| FC         | U-0000FC       | C3BC      | ü           |
| FD         | U-0000FD       | C3BD      | ý           |
| FE         | U-0000FE       | C3BE      | þ           |
| FF         | U-0000FF       | C3BF      | ÿ           |
+------------+----------------+-----------+-------------+
256 rows in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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