Storing ASCII Characters in Non-ASCII Columns

This section describes a test of storing ASCII characters into non-ASCII columns.

The first test I did is to store ASCII characters into columns defined for non-ASCII character sets like, Latin1, UTF-8, GBK, and Big5. The test was done with this PHP script:

<?php 
#- MySQL-Insert-ASCII.php
#- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
#
  $con = mysqli_connect("localhost", "Herong", "TopSecret");
  $ok = mysqli_select_db($con, "HerongDB");
  $test_name = "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', 
    'Television', 'Television', 'Television', 'Television')
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: ".$row['String_ASCII']."\n");
    print("   String_Latin1: ".$row['String_Latin1']."\n");
    print("   String_UTF8: ".$row['String_UTF8']."\n");
    print("   String_GBK: ".$row['String_GBK']."\n");
    print("   String_Big5: ".$row['String_Big5']."\n");
  }  
  mysqli_free_result($res);

  mysqli_close($con); 
?>

The output tells me that ASCCI characters can be stored in character sets: ascii, latin1, utf8, gbk, and big5, because they are all compatible with ASCII:

C:\herong> \local\php\php MySQL-Insert-ASCII.php
Number of rows deleted: 0
Number of rows inserted: 1

Test Name = ASCII Test
   String_ASCII: Television
   String_Latin1: Television
   String_UTF8: Television
   String_GBK: Television
   String_Big5: Television

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

MySQL - Character Set and Encoding

 Specifying Character Set for Text Columns

 Creating a Table with Multiple Character Sets

 Checking Character Set Setting

Storing ASCII Characters in Non-ASCII Columns

 Storing Non-ASCII Characters with Encoded Bytes

 Transmitting Non-ASCII Characters between PHP and MySQL

 Viewing Character Set Variables

 Non-ASCII Test Result Analysis

 Fetching Non-ASCII Text from MySQL

 MySQL Encoding Issue Example

 MySQL - Sending Non-ASCII Text to MySQL

 Retrieving Chinese Text from Database to Web Pages

 Input Chinese Text Data to MySQL Database

 Chinese Text Encoding Conversion and Corruptions

 Archived Tutorials

 References

 Full Version in PDF/EPUB