Sending Non-ASCII Text to MySQL in Latin1 Encoding

∟MySQL - Sending Non-ASCII Text to MySQL

∟Sending Non-ASCII Text to MySQL in Latin1 Encoding

This section provides a test on sending non-ASCII character strings to MySQL server in Latin1 encoding.

In the previous chapter, I found the answer to fetch non-ASCII characters properly from MySQL database. But I skipped question of how to send non-ASCII characters properly to MySQL database.

In the previous tutorial, non-ASCII characters were included in the SQL statement as HEX values of encoded bytes. This ensured that the entire SQL statement contains only ASCII characters and prevented any potential encoding conversions applied by MySQL server before executing the statement.

However entering encoded HEX values for non-ASCII text in SQL statements is not the right answer for large text paragraphs. I need to find a proper way to send non-ASCII text directly in the SQL statement to MySQL server.

The first test I did was to include Latin1, UTF-8, GBK and Big5 characters directly in the SQL INSERT statement. In this test, I want MySQL to use the default settings of character_set_client=latin1 and character_set_connection=latin1 to see how MySQL applies the conversion when executing the INSERT statement.

<?php 
#- MySQL-Send-Non-ASCII-Latin1.php
#- Copyright (c) 2015, HerongYang.com, All Rights Reserved.
#
  $con = mysql_connect("localhost", "Herong", "TopSecret");
  $ok = mysql_select_db("HerongDB", $con);
  $test_name = "Send Non-ASCII";

# Set character_set_results
  mysql_query("SET character_set_results=utf8", $con);

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

# Delete the record
  $sql = "DELETE FROM Comment_Mixed WHERE Test_Name ='$test_name'";
  mysql_query($sql, $con);
  print("\nNumber of rows deleted: ".mysql_affected_rows()."\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', 
    'T?l?vision', '电视机/電視機', '???', '???');
END_OF_MESSAGE;

# Run the SQL statement
  if (mysql_query($sql, $con)) {
    print("\nNumber of rows inserted: ".mysql_affected_rows()."\n");
  } else {
    print("SQL statement failed.\n");
    print(mysql_errno($con).": ".mysql_error($con)."\n"); 
  }

# Get the recod back
  $sql = "SELECT * FROM Comment_Mixed"
    . " WHERE Test_Name = '$test_name'";
  $res = mysql_query($sql, $con);
  if ($row = mysql_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");
  }  
  mysql_free_result($res);

  mysql_close($con); 
?>

Note that string literals in the source code above will not be displayed properly, because this book uses UTF-8 encoding.

Here is the output:

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

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

Number of rows deleted: 1

Number of rows inserted: 1

Test Name = Send Non-ASCII
   String_ASCII: 0x54656c65766973696f6e
   String_Latin1: 0x54c3a96cc3a9766973696f6e
   String_UTF8: 0xc3a7e2809dc2b5c3a8c2a7e280a0c3a6c593c2ba
      2fc3a9e280bac2bbc3a8c2a6e28093c3a6c2a9c5b8
   String_GBK: 0x3f3f3f3f3fc3ba
   String_Big5: 0x3f713f3f3fc3b7

The output is interesting:

Conversion from character_set_client, latin1, to character_set_connection, latin1 - Identical encodings. No conversion will happen.
Conversion from character_set_connection, latin1, to column String_ASCII's encoding, ascii - Will happen with potential data loss. But the input text for String_ASCII is ASCII characters only and compatible with Latin1. The output shows no problem.
Conversion from character_set_connection, latin1, to column String_Latin1's encoding, latin1 - Perfect match. No conversion will happen. Output of String_Latin1 is perfectly correct.
Conversion from character_set_connection, latin1, to column String_UTF8's encoding, utf8 - Will happen with potential data corruption. MySQL took my UTF-8 input string as a Latin1 string and converted to UTF8. Output of String_UTF8 is totally corrupted.
Conversion from character_set_connection, latin1, to column String_GBK's encoding, gbk - Will happen with potential data corruption. MySQL took my GBK input string as a Latin1 string and converted to GBK. Output of String_GBK is totally corrupted.
Conversion from character_set_connection, latin1, to column String_Big5's encoding, big5 - Will happen with potential data corruption. MySQL took my Big5 input string as a Latin1 string and converted to Big5. Output of String_Big5 is totally corrupted.

Last update: 2015.

Table of Contents

About This Book

PHP Installation on Windows Systems

Integrating PHP with Apache Web Server

charset="*" - Encodings on Chinese Web Pages

Chinese Characters in PHP String Literals

Multibyte String Functions in UTF-8 Encoding

Input Text Data from Web Forms

Input Chinese Text Data from Web Forms

MySQL - Installation on Windows

MySQL - Connecting PHP to Database