Storing Non-ASCII Characters with Encoded Bytes

∟Storing Non-ASCII Characters with Encoded Bytes

This section describes a test of storing non-ASCII characters with encoded bytes for columns with different encodings in MySQL server.

After created a table with columns with non-ASCII character sets, the next question is how to send non-ASCII characters to a MySQL server in the SQL statement.

The first test I did was to build a SQL INSERT statement with French and Chinese characters converted into binary data with Latin1, UTF-8, GBK and Big5 encodings. Then the SQL INSET statement is sent to my local MySQL server with mysqli_query() method for execution.

<?php 
#- MySQL-Insert-Non-ASCII.php
#- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
#
  $con = mysqli_connect("localhost", "Herong", "TopSecret");
  $ok = mysqli_select_db($con, "HerongDB");
  $test_name = "Non-ASCII Test";
  
# Delete the record
  $sql = "DELETE FROM Comment_Mixed WHERE Test_Name ='$test_name'";
  if (mysqli_query($con, $sql)) {
    print("Number of rows deleted: ".mysqli_affected_rows($con)."\n");
  } else {
    print("SQL statement failed.\n");
    print(mysqli_errno($con).": ".mysqli_error($con)."\n"); 
  }

# Build the SQL INSERT statement
  $sql = <<<END_OF_MESSAGE
INSERT INTO Comment_Mixed (Test_name, String_ASCII, 
    String_Latin1, String_UTF8, String_GBK, String_Big5)
  VALUES ('$test_name', 
     'Television', 
    X'54E96CE9766973696E6F', 
    X'E794B5E8A786E69CBA2FE99BBBE8A696E6A99F', 
    X'B5E7CAD3BBFA', 
    X'B971B5F8BEF7');
END_OF_MESSAGE;

# Run the SQL statement
  if (mysqli_query($con, $sql)) {
    print("Number of rows inserted: ".mysqli_affected_rows($con)."\n");
  } else {
    print("SQL statement failed.\n");
    print(mysqli_errno($con).": ".mysqli_error($con)."\n"); 
  }

# Get the recode back
  $sql = "SELECT * FROM Comment_Mixed"
    . " WHERE Test_Name = '$test_name'";
  $res = mysqli_query($con, $sql);
  if ($row = mysqli_fetch_array($res)) {
    print("\nTest Name = ".$row['Test_Name']."\n");
    print("   String_ASCII: 0x".bin2hex($row['String_ASCII'])."\n");
    print("   String_Latin1: 0x".bin2hex($row['String_Latin1'])."\n");
    print("   String_UTF8: 0x".bin2hex($row['String_UTF8'])."\n");
    print("   String_GBK: 0x".bin2hex($row['String_GBK'])."\n");
    print("   String_Big5: 0x".bin2hex($row['String_Big5'])."\n");
  }  
  mysqli_free_result($res);

  mysqli_close($con); 
?>

Here is the output of my script running on PHP 7 and MySQL 8:

Number of rows deleted: 0
Number of rows inserted: 1

Test Name = Non-ASCII Test
   String_ASCII: 0x54656c65766973696f6e
   String_Latin1: 0x54c3a96cc3a9766973696e6f
   String_UTF8: 0xe794b5e8a786e69cba2fe99bbbe8a696e6a99f
   String_GBK: 0xe794b5e8a786e69cba
   String_Big5: 0xe99bbbe8a696e6a99f

Comparing with the binary value I provided in my script, I see that:

The test was successful on with columns defined as ASCII or UTF-8.
The test failed with columns defined as Latin1, GBK, or Big5. I didn't get the same binary values I provided in my script from the SQL query statement. See the next section for explanations.

It is interesting to know that the output of my script running on PHP 5 and MySQL 5 was different.

C:\herong> \local\php5\php MySQL-Insert-Non-ASCII.php

Number of rows deleted: 0
Number of rows inserted: 1

Test Name = Non-ASCII Test
   String_ASCII: 0x54656c65766973696f6e
   String_Latin1: 0x54e96ce9766973696e6f
   String_UTF8: 0x3f3f3f2f3f3f3f
   String_GBK: 0x3f3f3f
   String_Big5: 0x3f3f3f

The output from PHP 5 and MySQL 5 tells me that:

The test was successful on with columns defined as ASCII or Latin1.
The test failed with columns defined as UTF-8, GBK, or Big5. I didn't get the same binary values as the SQL statement. See the next section for explanations.