This section describes how to view the current settings of MySQL session variables involved in character set conversions with client programs.
From the previous section, we know that MySQL 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) 2007 by Dr. Herong Yang, http://www.herongyang.com/
-- 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:\>\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:
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 - Potential data loss.
Conversion from character_set_connection, latin1, to column String_Latin1's encoding, latin1 - Perfect match. No conversion will happen.
Conversion from character_set_connection, latin1, to column String_UTF8's encoding, utf8 - Impossible to send any multiple-byte UTF8 characters.
Conversion from character_set_connection, latin1, to column String_GBK's encoding, gbk - Impossible to send any multiple-byte GBK characters.
Conversion from character_set_connection, latin1, to column String_Big5's encoding, big5 - Impossible to send any multiple-byte Big5 characters.
Conversion from column String_ASCII's encoding, ascii, to character_set_results, latin1 - Latin1 is a superset of ASCII. No conversion will happen.
Conversion from column String_Latin1's encoding, latin1, to character_set_results, latin1 - Perfect match. No conversion will happen.
Conversion from column String_UTF8's encoding, utf8, to character_set_results, latin1 - All multiple-byte UTF8 characters lost.
Conversion from column String_GBK's encoding, gbk, to character_set_results, latin1 - All multiple-byte GBK characters lost.
Conversion from column String_Big5's encoding, big5, to character_set_results, latin1 - All multiple-byte Big5 characters lost.
