I have few databases in latin1 and i will migrate it to utf8. I have some characters like 'œ' that are utf8 characters.
I want to know how mysql store the encoding ?
Cause if it stores utf8 characters in my latin1 database i don't need to dump it and import it but i just need to change the charset and collate of my database right ?
Many thanks for your answers
If your table is defined using latin1 as its character set, I sure hope it's not trying to store utf8 characters. It won't work.
œ is a character in the latin1 encoding, it's a character with byte value 156. Whereas the same glyph in utf8 is two bytes, c29c.
You can convert one column to another character set like this:
ALTER TABLE <tablename> MODIFY COLUMN <columnname> VARCHAR(...) CHARACTER SET uf8mb4;
You would have to know its data type, and other column options like NOT NULL or DEFAULT.
You can convert all string columns of a table in one alter like the following:
ALTER TABLE <tablename> CONVERT TO CHARACTER SET utf8mb4;
It's convenient to do this because you don't have to repeat all the other column attributes.
Converting the character set should rewrite all the glyphs in the new encoding. If it can't, it will return an error and cancel the alter without changing your table.
MySQL continues to handle CHARACTER SET latin1. (And I expect it to support it "forever".) The default was changed to utf8mb4 in MySQL 8.0.
œ is hex 9C in latin1, so I don't understand the issue with it.
When SELECTing or INSERTing data from/into a table, the source and destination do not need to use the same CHARACTER SET; MySQL will convert the encoding based as needed.
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;
changes all the varchar/text columns from to the CHARACTER SET utf8mb4. Caution: 5.6 and 5.7 had length problems; let's see SHOW CREATE TABLE and what conversion you are attempting. It works "in place" without needing to dump and reload. (But it will block access to the table for some time.)
latin1 is limited to 1-byte encoding; this includes less than 128 accented letters (plus your œ). MySQL's utf8 is not quite all Unicode characters; each "character" takes up to 3 _bytes). utf8mb4 takes up to 4 bytes each, and adds coverage of Emoji and more of Chinese.
