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.
- Copy the above PHP script and paste in a file say, ‘dbconversion.php’.
- Now put this file in your server (development/production).
- Run this script from ‘yourdomain.com/dbconversion.php’.
- 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
Leave a Reply