Mysql server's encoding differs from the client's (latin1 vs utf8mb4). How bad is it?

Question

I'm designing a php web-app and have some difficulties understanding a meaning of Mysql variables related to encoding and how they interact between each other. The encoding of the server is set to latin1 but the client's is utf8mb4.

Running the mysql query inside a database

SHOW VARIABLES
WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'

gives the following:

character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_database = latin1
character_set_filesystem = binary
character_set_results = utf8mb4
character_set_server = latin1
character_set_system = utf8
collation_connection = utf8mb4_unicode_ci
collation_database = latin1_swedish_ci
collation_server = latin1_swedish_ci

I'm afraid running into issues with the older databases which are in latin1 if I change the character set of the mysql server to utf8mb4, but I certainly want to use utf8mb4 for the new databases I create. To correctly serve and retrieve data from the database should server's and client's encoding and collation always be the same? Any insight would be appreciated?

You set the encoding of tables/columns you create when you create them = that decides what characters you can store. You set the *connection encoding*, i.e. the encoding you're going to use while talking to the database, when establishing the connection. The server defaults are pretty irrelevant most of the time for all of this. — deceze, Dec 21 '17 at 15:14

score 0 · Answer 1 · answered Dec 27 '17 at 01:42

Some of those VARIABLES must agree with what encoding is used in the client.

CREATE TABLE ... specifies how they are to be stored in the tables.

If those two differ, then MySQL will convert "on the wire" between the client encoding an the table encoding.

If that means converting, say, Korean characters (encoding in utf8 or utf8mb4) to latin1 encoding, it will not be possible. On the other hand, all accented letters in Western Europe have encodings in both latin1 and utf8, so there is no problem.

Read this for common screwups: Trouble with UTF-8 characters; what I see is not what I stored

See ALTER TABLE .. CONVERT TO .. for converting all character columns in one table to a different encoding (assuming it was correctly stored to begin with).

Mysql server's encoding differs from the client's (latin1 vs utf8mb4). How bad is it?

1 Answers1