How mysql store data and their encoding



  • 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


  • QA Engineer

    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  MODIFY COLUMN  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  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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2