MySql database at the moment has:

  • Character Set: utf8
  • Default Collation Name: utf8_unicode_ci

I want to change it to:

  • utf8mb4
  • utf8mb4_unicode_ci

I would also do the same thing for all tables in the database.

My question is the following:

  • By doing that change is there a possibility that the content of my columns gets messed up in any way? (Cause I am really scared to do it!!!)
  • I have millions of rows in the tables, is there ANY way to do a check after I make the switch that the content of all tables is the OK?
    • Some crazy ideas I am thinking of include:
      • Running some scripts to compare original DB to changed DB
      • Running some scripts to calculate column sizes of original DB to changed DB

Any help or ideas which would guide me to the right direction would be appreciated.


2 Answers 2


Main thing to watch altering tables is the index length will increase for utf8 indexed columns which might put them over limit. Adjust URL for your mysql version as some differences apply. These index length exceeded errors will show up immediately on ALTER TABLE so this will be a final bit of the migration.

On validation, generally won't be a problem. Try this on a few tables to get confidence in the idea confidence on smaller tables:

   CREATE TABLE tbl_new LIKE tbl
   ALTER TABLE tbl_new ..  {change to utf8mb4 fields}
   INSERT INTO tbl_new SELECT * FROM tbl
   RENAME TABLE tbl_new TO tbl, tbl TO tbl_old

A SELECT ... tbl JOIN tbl_old ON tbl.pk = tbl_old.pk WHERE tbl.utf_field != tbl_old.utf_field can be used to validate values.

Application behavior can be checked, and you can RENAME back if their are problems. (taking to account updates).

  • Thank you so much @danblack. Sounds logical. Will try that. May 9, 2020 at 1:05
  • @VickyDallas If you find this answer to be either helpful or correct (or both) please consider marking it as such - this subsequently helps those with similar problems to find helpful or correct answers!
    – Vérace
    May 9, 2020 at 6:52

For each table:


That changes all string columns in one table. If you have any columns that should not be utf8mb4, evasive measures must be taken.

If you blindly use VARCHAR(255) and are using MySQL 5.5 or 5.6, there could be index problems. Here are some workarounds: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

You might consider this newer collation: utf8mb4_unicode_520_ci

  • Should it not be ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ?? Jun 6, 2023 at 15:04
  • Do you need CONVERT TO for utf8 → utf8mb4? Isn't the former a subset of the latter, and so valid? Jun 20, 2023 at 13:53
  • @artfulrobot - Please elaborate; I don't understand your point.
    – Rick James
    Jun 20, 2023 at 14:48
  • @RickJames I though that urf8 strings would be valid utfmb4 strings (but not other way around) such that meerly telling the db to treat the data as utf8mb4 would be enough now - new unicode chars could be added; old ones would just work, without the need of a conversion. I think I'm wrong (having tried it!) but that was my reasoning. Jun 21, 2023 at 10:59
  • You have already lost any Emoji that were previously put into the utf8 columns. The CONVERT TO will allow future INSERTs to insert Emoji and other 4-byte utf8mb4 characters.
    – Rick James
    Jun 21, 2023 at 16:08

Your Answer

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

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