Viewing Character Set Variables

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) 2015, 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:\>\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:

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

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 - Sending Non-ASCII Text to MySQL

 Retrieving Chinese Text from Database to Web Pages

 Input Chinese Text Data to MySQL Database

 References

 PDF Printing Version