What is the definition of latin1 character set in MySQL 8.0 and MySQL 5.7?

Question

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.

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

Answer 2

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)