Viewing Character Set Variables

∟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) 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 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.

Conversion from character_set_client, cp850, to character_set_connection, cp850 - Identical encodings. No conversion will happen.
Conversion from character_set_connection, cp850, to column String_ASCII's encoding, ascii - Potential data loss.
Conversion from character_set_connection, cp850, to column String_Latin1's encoding, latin1 - Potential data loss.
Conversion from character_set_connection, cp850, to column String_UTF8's encoding, utf8 - Seems to be ok, because utf8 is compatible with cp850.
Conversion from character_set_connection, cp850, to column String_GBK's encoding, gbk - Impossible to send any multiple-byte GBK characters.
Conversion from character_set_connection, cp850, to column String_Big5's encoding, big5 - Impossible to send any multiple-byte Big5 characters.

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

Conversion from column String_ASCII's encoding, ascii, to character_set_results, cp850 - cp850 is a superset of ASCII. No conversion will happen.
Conversion from column String_Latin1's encoding, latin1, to character_set_results, cp850 - Potential data loss.
Conversion from column String_UTF8's encoding, utf8, to character_set_results, cp850 - Seems to be ok, because utf8 is compatible with cp850.
Conversion from column String_GBK's encoding, gbk, to character_set_results, cp850 - All multiple-byte GBK characters lost.
Conversion from column String_Big5's encoding, big5, to character_set_results, cp850 - All multiple-byte Big5 characters lost.

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.