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) 2007, 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 command line tool gives me this output on MySQL 8:

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

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

+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | cp850                        |
| character_set_connection | cp850                        |
| character_set_database   | utf8mb4                      |
| character_set_filesystem | binary                       |
| character_set_results    | cp850                        |
| character_set_server     | utf8mb4                      |
| 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(356) DEFAULT NULL,
  `String_ASCII` varchar(256) CHARACTER SET ascii 
     COLLATE ascii_general_ci DEFAULT NULL,
  `String_Latin1` varchar(256) CHARACTER SET latin1 
     COLLATE latin1_swedish_ci DEFAULT NULL,
  `String_UTF8` varchar(256) CHARACTER SET utf8 
     COLLATE utf8_general_ci DEFAULT NULL,
  `String_GBK` varchar(256) CHARACTER SET gbk 
     COLLATE gbk_chinese_ci DEFAULT NULL,
  `String_Big5` varchar(256) CHARACTER SET big5 
     COLLATE big5_chinese_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------------+----------------------------------------------
1 row in set (0.00 sec)

Based on the current character set variables, MySQL server will do the following character set encoding conversions during the data insert or update process.

Based on the current character set variables, MySQL server will do the following character set encoding conversions during the data retrieval process.

By the way, if you are using different version of MySQL, you may have different default values from character set variables. That will result different conversion issues.

Last update: 2019.

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

 Archived Tutorials

 References

 Full Version in PDF/EPUB