How to update strange characters in MySQL?

Question

I am facing this problem with my database server. First, I bought a server from Godaddy they have database server following:

Server: Localhost via UNIX socket
Server type: MySQL
Server connection: SSL is not being used Documentation
Server version: 5.6.51-cll-lve - MySQL Community Server (GPL)
Protocol version: 10
Server charset: cp1252 West European (latin1)

So when I enter Chinese characters from my website it appears like this in the database

 美嘉11c10 

But it shows right when I retrieve those data from the database and show them on the website again. Like this:

 你好世界 

Now, My problem is, I have changed my hosting server. I am on the Bluehost server now. They have different database server like this:

Server: Localhost via UNIX socket
Server type: Percona Server
Server connection: SSL is not being used Documentation
Server version: 5.7.23-23 - Percona Server (GPL), Release 23, Revision 500fcf5
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)

It doesn't work like Godaddy's server.

I can't change my database server charset from PHPMyAdmin on Bluehost. I am open up to any solutions. It will be better if I update the strange characters and make them Chinese characters again.

Answer 1

You must use utf8mb4 -- both for the columns that might contain Chinese characters and for the connection (assuming the client is using UTF-8).

Latin1 will not work. cp1252 will not work. MySQL's "utf8" does not contain all Chinese characters; utf8mb4 is needed instead.

More discussion: Trouble with UTF-8 characters; what I see is not what I stored

If you provide a sample of SELECT HEX(col) ... and the Chines that you are expecting, I can probably provide a "fix" for the data. For example:

CONVERT(BINARY(CONVERT('好世界' USING latin1)) USING utf8mb4) --> '好世界'

I would feel safer trying to "fix" the text in SQL than in the client (eg Python).