Archived: Character Set Variables on MySQL 5

This section describes how to view the current settings of MySQL session variables involved in character set conversions with client programs on MySQL 5.

From the previous section, we know that MySQL 5 uses 3 session variables to control character set conversions when receiving SQL statements from the client program, executing SQL statements, and returning result set to the client program. Here is simple SQL script I used to view the default settings of those character set encoding variables and the table column character set encodings:

-- MySQL-View-Character-Set-Variables.sql
-- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
-- To run this script at mysql> prompt, use the "source" command:
--   mysql> source MySQL-View-Character-Set-Variables.sql

-- Set current database
USE HerongDB;

-- Show character set encoding variables
SHOW VARIABLES LIKE 'character_set_%';

-- Show table column character set encodings
SHOW CREATE TABLE Comment_Mixed;

Running this script with MySQL Monitor gives me this output:

C:\herong> \local\mysql\bin\mysql -u Herong -pTopSecret

mysql> SOURCE MySQL-View-Character-Set-Variables.sql
Database changed

+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | latin1                       |
| character_set_connection | latin1                       |
| character_set_database   | latin1                       |
| character_set_filesystem | binary                       |
| character_set_results    | latin1                       |
| character_set_server     | latin1                       |
| character_set_system     | utf8                         |
| character_sets_dir       | \local\mysql\share\charsets\ |
+--------------------------+------------------------------+
8 rows in set (0.00 sec)

+---------------+----------------------------------------------
| Table         | Create Table
+---------------+----------------------------------------------
| Comment_Mixed | CREATE TABLE `comment_mixed` (
  `Test_Name` varchar(256) default NULL,
  `String_ASCII` varchar(256) character set ascii default NULL,
  `String_Latin1` varchar(256) default NULL,
  `String_UTF8` varchar(256) character set utf8 default NULL,
  `String_GBK` varchar(256) character set gbk default NULL,
  `String_Big5` varchar(256) character set big5 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------------+----------------------------------------------
1 row in set (0.00 sec)

Based on the current character set variables, MySQL server will do the following character set encoding conversions:

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

 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

 Archived: Download and Install PHP 5.2.2 on Windows

 Archived: Downloading Apache 2.2.4 Binary for Windows

 Archived: Installing Apache 2.2.4 on Windows Systems

 Archived: MySQL 5.0 Download, Installation and Start

 Archived: php_mysql.dll - Configuring PHP to Use MySQL Extension

 Archived: Commonly Used php_mysql.dll Functions

 Archived: mysql_connect() - Creating MySQL Connections

Archived: Character Set Variables on MySQL 5

 Archived: Non-ASCII Test Analysis on MySQL 5

 Archived: Fetching Non-ASCII Text from MySQL 5

 Archived: Sending Text in Latin1 Encoding to MySQL 5

 Archived: Sending Text in UTF8 Encoding to MySQL 5

 References

 Full Version in PDF/EPUB