Fetching Non-ASCII Text from MySQL

∟Fetching Non-ASCII Text from MySQL

This section describes how to set character_set_results properly to retrieve non-ASCII character strings from MySQL server.

Based on the analysis provided in the previous section, I revised my PHP script to set character_set_results to Latin1. Hope this will allow me to fetch non-ASCII characters correctly from the String_Latin1 column:

<?php 
#- MySQL-Fetch-Non-ASCII-Latin1.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";

# Set character_set_results
  $sql = "SET character_set_results=Latin1";
  mysqli_query($con, $sql);

# Show character set encoding variables
  $sql = "SHOW VARIABLES LIKE 'character_set_%'";
  $res = mysqli_query($con, $sql);
  while ($row = mysqli_fetch_array($res)) {
    print($row['Variable_name']." = ".$row['Value']."\n");
  }  
  mysqli_free_result($res);

# Delete the record
  $sql = "DELETE FROM Comment_Mixed WHERE Test_Name ='$test_name'";
  mysqli_query($con, $sql);
  print("\nNumber of rows deleted: ".mysqli_affected_rows($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("\nNumber 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); 
?>

The output confirms that character_set_results=Latin1 helped to stop the wrong conversion on non-ASCII characters fetched from the String_Latin1 column:

C:\herong> \local\php\php MySQL-Fetch-Non-ASCII-Latin1.php

character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_database = utf8mb4
character_set_filesystem = binary
character_set_results = latin1
character_set_server = utf8mb4
character_set_system = utf8
character_sets_dir = \local\mysql\share\charsets\

Number of rows deleted: 1

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

However, other encoding conversions were performed on other non-ASCII columns:

ASCII characters fetched from String_ASCII column were converted to Latin1. No problem.
Latin1 characters fetched from String_Latin1 column were converted to Latin1. No problem.
UTF-8 characters fetched from String_UTF8 column were converted to Latin1. MySQL failed with a sequence of 0x3f bytes.
GBK characters fetched from String_GBK column were converted to Latin1. MySQL failed with a sequence of 0x3f bytes.
Big5 characters fetched from String_Big5 column were converted to Latin1. MySQL failed with a sequence of 0x3f bytes.

My conclusion is that:

If you are working with a single character set encoding, set character_set_results to that encoding.
If you are working with multiple character set encodings, then you need to run multiple SQL statements with different character_set_results setting each time to get correct result.
Bottom line, use UTF-8 encoding for all columns and in all settings. You should have no conversion problem.