How to solve chinese became question mark after database migration?

A few days ago, I moved one website from production environment to local for modification. The local environment was build with wampserver three years ago, mysql version is out dated.
I got “Unknown collation: utf8mb4_unicode_ci” error so I exported mysql with

mysqldump --compatible=mysql4

After finished modifying, I moved the website back online. During testing I found chinese would become question mark after clicking publish button.
It looks like a database issue and it is. All tables collation was set to latin1_swedish_ci.
Alter table in adminer GUI is inefficient because columns charset need to be set to utf8mb4 too.
Using command is quicker:

alter table convert to character set utf8mb4 collate utf8mb4_unicode_ci;

But it still need to alter table one by one.
The quickest way I found is to search and replace exported sql file with:

Replace DEFAULT CHARSET=latin1; with DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

then import it again.

Leave a Reply

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