MySQL Encoding Issue Example

∟MySQL Encoding Issue Example

This section provides an example of MySQL server encoding issue with Chinese characters, when incorrectly encoded string is stored in database and wrong character_set_* settings are used.

Here is a real example of MySQL encoding issue I have encountered recently. Take a look at the following query statement used in a PHP script and its output:

SELECT Code FROM Dictionary WHERE Code = '鸡'

Code: (踡)

Why does MySQL server match and return a Chinese character that is different than the character given in the query statement?

To find out the root cause of the problem, I modified the query statement as shown below:

SELECT Code, HEX(Code) AS CodeHex, HEX('踡') AS OutHex, HEX('鸡') AS InHex 
  FROM Dictionary WHERE Code = '鸡'

Code: (踡)
CodeHex: (C3A8C2B8C2A1)
OutHex: (E8B8A1)
InHex: (E9B8A1)

Based on my knowledge to Chinese character encodings, I can see what really happened when the above statement was executed.

1. Since Hex string 0xC3A8C2B8C2A1 from the database was able to be displayed as correct Chinese character of 踡, I can assume that the following:

The column Dictionary.Code is defined as "UTF8" encoding, and the MySQL Server setting "character_set_results" was defined as "Latin1" encoding.
The Hex string 0xC3A8C2B8C2A1 was converted from "UTF8" encoding to "Latin1" encoding, and became 0xE8B8A1.
The PHP script was running with "UTF8" as the default encoding, so 0xE8B8A1 was displayed correctly as 踡.

2. Since the condition "Code = '鸡'" results to "True", I can also assume that the following:

The MySQL Server setting "character_set_client" was defined as "Latin1" encoding, and the MySQL Server setting "character_set_connection" was defined as "Latin1" encoding.
The query character 鸡 was delivered to the MySQL server correctly in "UTF-8" encoding, 0xE9B8A1. This can be confirmed by the output line "InHex: (E9B8A1)".
When the condition "Code = '鸡'" was evaluated, the Hex string 0xE9B8A1 was converted from "Latin1" encoding to "UTF8" encoding, and became 0xC3A9C2B8C2A1.
Then the Hex string 0xC3A9C2B8C2A1 resulted from the query character 鸡 is compared with the Hex string 0xC3A8C2B8C2A1 stored in column Dictionary.Code.
The comparison was does in "UTF-8" encoding. And the resulting Boolean was "True" unfortunately, even those two Hex strings are different.

To validate my assumptions, I wrote the following simulation PHP script.

<?php 
#- MySQL-Encoding-Issue.php
#- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
  mb_internal_encoding("utf8");
  $con = mysqli_connect('127.0.0.1', 'test', 'test', 'test');

  mysqli_query($con, "SET character_set_results=latin1");
  mysqli_query($con, "SET character_set_connection=latin1");
  mysqli_query($con, "SET character_set_client=latin1");

  $res = mysqli_query($con, "SHOW VARIABLES LIKE 'character_set_%'");
  while ($row = mysqli_fetch_array($res)) {
    print($row['Variable_name'].": ".$row['Value']."\n");
  }
  mysqli_free_result($res);

  mysqli_query($con, "DROP TABLE Dictionary");
  mysqli_query($con, 
    "CREATE TABLE Dictionary (Code VARCHAR(64)) DEFAULT CHARSET=utf8");
  mysqli_query($con, 
    "INSERT INTO Dictionary (Code) VALUES (X'C3A8C2B8C2A1')");

  $sql = "SELECT Code, HEX(Code) AS CodeHex,"
    . " HEX('踡') AS OutHex, HEX('鸡') AS InHex" 
    . " FROM Dictionary WHERE Code = X'C3A9C2B8C2A1'";
  $res = mysqli_query($con, $sql);
  $row = mysqli_fetch_array($res);
  print("SQL: (".$sql.")\n");
  print("Code: (".$row['Code'].")\n");
  print("CodeHex: (".$row['CodeHex'].")\n");
  print("OutHex: (".$row['OutHex'].")\n");
  print("InHex: (".$row['InHex'].")\n");
  mysqli_free_result($res);

  $sql = "SELECT IF(CONVERT(X'C3A8C2B8C2A1' USING utf8)"
    . " = CONVERT(X'C3A9C2B8C2A1' USING utf8), 'True', 'False')"
    . " AS Comparison";
  $res = mysqli_query($con, $sql);
  $row = mysqli_fetch_array($res);
  print("SQL: (".$sql.")\n");
  print("Comparison: (".$row['Comparison'].")\n");
  mysqli_free_result($res);

  mysqli_close($con);
?>

You can try the above PHP script on your MySQL server. And you will get similar output shown below:

herong$ php MySQL-Encoding-Issue.php 

character_set_client: latin1
character_set_connection: latin1
character_set_database: utf8mb4
character_set_filesystem: binary
character_set_results: latin1
character_set_server: utf8
character_set_system: utf8
character_sets_dir: /usr/local/mysql-8.0.17-.../share/charsets/

SQL: (SELECT Code, HEX(Code) AS CodeHex, HEX('踡') AS OutHex, HEX('鸡') 
  AS InHex FROM Dictionary WHERE Code = X'C3A9C2B8C2A1')
Code: (踡)
CodeHex: (C3A8C2B8C2A1)
OutHex: (E8B8A1)
InHex: (E9B8A1)

SQL: (SELECT IF(CONVERT(X'C3A8C2B8C2A1' USING utf8) 
  = CONVERT(X'C3A9C2B8C2A1' USING utf8), 'True', 'False') AS Comparison)
Comparison: (True)

Obviously, the issue can be resolved by:

Replacing the incorrect encoding string 0xC3A8C2B8C2A1 with 0xE8B8A1, which is the correct "UTF8" encoding of the Chinese character.
Setting "character_set_results" to "utf8", to stop MySQL server output getting converted to other encodings
Setting "character_set_connection" and "character_set_client" to "utf8", to stop MySQL server input getting converted to other encodings.

However, one thing that I will don't understand today is why MySQL server evaluate Boolean expression of "CONVERT(X'C3A8C2B8C2A1' USING utf8) = CONVERT(X'C3A9C2B8C2A1' USING utf8)" to true. Please share it with me, if you know the answer.