Non ASCII Characters with MySQL

Part:   1  2  3 

(Continued from previous part...)

Transmitting Non ASCII Characters to the Server

Handling non ASCII characters with MySQL not only requires us setting up the table columns with the correct encoding (character set), but also requires us setting up the correct encoding for transferring characters to and from the database.

MySQL offers the following variables to control the encodings used to transfer characters between the client (PHP script) and the server (MySQL database):

• character_set_server - Character set used for all databases on the server.
• character_set_database - Character set used for the default databases on the server.
• character_set_client - Character set used by the client for all SQL statements.
• character_set_connection - Character set that all SQL statements should be converted to by the server before executing them.
• character_set_results - Character set that all returning result should be converted to by the server before sending it back to the client.

In most cases, you should make the client (PHP script) to use the same encoding (character set) as the server (MySQL server), so that there is no need to convert when transferring characters between them.

Some times, you may need to use different encodings. For example, you want to use UTF-8 for your PHP scripts, while using GB2312 for your MySQL tables.

You can use two special SET commands to change those variables:

1. "SET NAMES 'x'" is equivalent to these three statements:

   SET character_set_client = x;
   SET character_set_results = x;
   SET character_set_connection = x;

2. "SET CHARACTER SET x" is equivalent to these three statements:

   SET character_set_client = x;
   SET character_set_results = x;
   SET collation_connection = @@collation_database;

You can always use the SHOW VARIABLES LIKE 'variable_name' to view the current value of the given variable.

MySqlUnicode.php - UTF-8 Sample Script

My is my sample script to show you how to send UTF-8 strings to a MySQL server and store them in UTF-8 encoding, MySqlUnicode.php:

<?php # MySqlUnicode.php
# Copyright (c) 2006 by Dr. Herong Yang, http://www.herongyang.com/
# 
   $con = mysql_connect('localhost');
   print "\nDefault settings...\n";
   $rs = mysql_query("SHOW VARIABLES LIKE 'character_set_%'");
   while ($row = mysql_fetch_array($rs)) {
      print "    ".$row[0].'   '.$row[1]."\n";
   }

   print "\nUpdated settings...\n";
   $rs = mysql_query("SET NAMES 'utf8'");
   $rs = mysql_query("SHOW VARIABLES LIKE 'character_set_%'");
   while ($row = mysql_fetch_array($rs)) {
      print "    ".$row[0].'   '.$row[1]."\n";
   }

   print "\nCreating a table in UTF-8...\n";
   $rs = mysql_query('DROP DATABASE MyBase');
   $rs = mysql_query('CREATE DATABASE MyBase CHARACTER SET utf8');
   $rs = mysql_query('USE MyBase');
   $rs = mysql_query('CREATE TABLE MyTable (ID INTEGER,'
      .' Message VARCHAR(80))');

   print "\nInserting some rows to the table...\n";
   $str = "Hello!";
   $rs = mysql_query("INSERT INTO MyTable VALUES ( 1, '$str')");
   $str = "\xC2\xA1Hola!";
   $rs = mysql_query("INSERT INTO MyTable VALUES ( 2, '$str')");
   $str = "\xE4\xBD\xA0\xE5\xA5\xBD!";
   $rs = mysql_query("INSERT INTO MyTable VALUES ( 3, '$str')");
      
   print "\nQuery some rows from the table...\n";
   $rs = mysql_query('SELECT * FROM MyTable WHERE ID < 10');
   print "   ".mysql_field_name($rs,0)."   "
      .mysql_field_name($rs,1)."\n";
   while ($row = mysql_fetch_array($rs)) {
      print "    ".$row[0].'   '.$row[1]."=(\x".bin2hex($row[1]).")\n";
   }
   mysql_free_result($rs);   
   mysql_close($con);
?>

(Continued on next part...)

Part:   1  2  3