How to convert MySQL collation from utf8mb4 to utf8

Recently I faced a tough situation with one of my WordPress site at the time of exporting MySQL database. My development MySQL server supports utf8mb4 collation but the production MySQL server does not support that. So, I had to convert all the tables of the MySQL database from  utf8mb4 to utf8.  The actual task was conversion from utf8mb4_unicode_ci to utf8_general_ci

I tried to convert manually several times but it did not work. My question was what is the difference between utf8mb4 and utf8?

utf8mb4 (as well as standard UTF-8) can directly store any character specified by Unicode; the former is fixed size at 4 bytes per character whereas the latter is between 1 and 4 bytes per character.

utf8 can only store the first 65,536 codepoints, which will cover CJVK (Chinese, Japanese, Vietnam, Korean), and use 1 to 3 bytes per character.

So, the real process will be ALTER the database as well as each database table to change the CHARACTER SET.  Using a PHP script I made the conversion and it worked perfectly. This is the PHP script which will convert your MySQL database collation from utf8mb4_unicode_ci to utf8_general_ci instantly. 

  1. Copy the above PHP script and paste in a file say, ‘dbconversion.php’.
  2. Now put this file in your server (development/production).
  3. Run this script from ‘’.
  4. It’s all. It will convert everything and you will get a success message.

Note: If you are using WordPress website and you need to convert your database any time, make sure ‘utf8’ Charset will be defined in your ‘wp-config.php’ file as
define(‘DB_CHARSET’, ‘utf8’);






18 responses to “How to convert MySQL collation from utf8mb4 to utf8”

  1. Lance avatar

    The code worked, this is really help me out, thanks a lot.

  2. alnork avatar

    Sencillamente genial… Thanks!

  3. Raghu Tumkur avatar

    SOS script. Thanks it Really saved.

  4. Simon avatar

    Amazing, life-saving, thanks a lot !!!!!!!!!!!!!!!!

  5. brent avatar

    nice catch on the change in the config file, I had missed that !

  6. Photis avatar

    Nice Job mate…. Thanks alot

  7. Tobias Bechtle avatar
    Tobias Bechtle

    Hey Sanjay, that script worked perfectly! Thank you a lot, it saved me a ton of time!

  8. Guy avatar

    Thx for the script … saved me a lot of time today 🙂

  9. Willem avatar

    Thank You so Much!! I have searched everywhere for a solution. This is the only one that works!! Thank Goodness!!

  10. vntesters avatar

    Thanks, it worked fine but “term_relationships”. How to convert this table please?

  11. yoyo2030 avatar


    How long does it take to convert the database? I got the success message but when I tried to upload the db to the new server still getting the error. My database is very big, like 200 M

    1. Sanjay Bhowmick avatar
      Sanjay Bhowmick

      Generally you can not upload 200M file into any PHPMyAdmin. You have to edit your php.ini file and set the maximum values for
      and then restart your webserver.

  12. Grupo One avatar

    ¡¡¡¡GENIUS!!!! Thanks a lot.

  13. grizzkN M Pattnaik avatar
    grizzkN M Pattnaik

    I tried this script on my localhost installation and got a success message. But the table names in the database still showed ‘utf8mb4_unicode_520_ci’ and when I tried to migrate the website with Duplicator plugin I got database errors ‘unknown collation’, The migration failed. What else do I need to do for a successful migration. I do not know any programming but can handle some code editing.
    Hope you can help.

    1. Sanjay Bhowmick avatar
      Sanjay Bhowmick

      Email me your sql file at and I shall convert it for you.

      1. N M Pattnaik avatar
        N M Pattnaik

        Thanks for the offer. I have sent you the file. Still, it would be nice if you can please post the steps for doing it. I will be needing it more than once and others might too.

      2. Wasif chowdhury avatar
        Wasif chowdhury


  14. mario duguran avatar

    wow it works!..thanks

Leave a Reply

Your email address will not be published. Required fields are marked *