查看 MySQL 字符集的方法_查看mysql 数据库的字符挤-CSDN博客

本文链接：https://blog.csdn.net/leo__1990/article/details/93504528

该博客主要围绕MySQL字符集展开，包含字符集设置，以及登陆数据库后查看服务器、所有数据库、数据表和数据库列的字符集等内容，为MySQL字符集相关操作提供指引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 MySQL字符集设置

系统变量：
– character_set_server：默认的内部操作字符集
– character_set_client：客户端来源数据使用的字符集
– character_set_connection：连接层字符集
– character_set_results：查询结果字符集
– character_set_database：当前选中数据库的默认字符集
– character_set_system：系统元数据(字段名等)字符集
– 还有以collation_开头的同上面对应的变量，用来描述字符序。

2 登陆MySQL数据库

[root@www.cndba.cn ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or /g.
Your MariaDB connection id is 12
Server version: 10.2.13-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

MariaDB [(none)]>

3 查看MySQL数据库服务器和数据库MySQL字符集。

MariaDB [test]> SHOW VARIABLES LIKE 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | /usr/local/mariadb-10.2.13-linux-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

4 查看mysql 所有数据库字符集

MariaDB [test]> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME,SQL_PATH from information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+----------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------------+----------------------------+------------------------+----------+
| information_schema | utf8                       | utf8_general_ci        | NULL     |
| mysql              | utf8                       | utf8_general_ci        | NULL     |
| performance_schema | utf8                       | utf8_general_ci        | NULL     |
| test               | utf8                       | utf8_general_ci        | NULL     |
+--------------------+----------------------------+------------------------+----------+
4 rows in set (0.00 sec)

5 查看mysql数据表的字符集

MariaDB [test]> show table status from test like '%t6%'/G;
*************************** 1. row ***************************
           Name: t6
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 16384
    Data_length: 49152
Max_data_length: 0
   Index_length: 0
      Data_free: 3152019456
 Auto_increment: NULL
    Create_time: 2018-03-22 03:26:09
    Update_time: NULL
     Check_time: NULL
      Collation: gbk_chinese_ci
       Checksum: NULL
 Create_options: partitioned
        Comment: 
1 row in set (0.00 sec)

ERROR: No query specified

6 查看mysql 数据库列的字符集

MariaDB [test]> show full columns from test.books;
+-------------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field       | Type        | Collation      | Null | Key | Default | Extra | Privileges                      | Comment |
+-------------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| id          | varchar(8)  | gbk_chinese_ci | NO   | PRI |         |       | select,insert,update,references |         |
| name        | varchar(24) | gbk_chinese_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| title       | varchar(96) | gbk_chinese_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| price       | float       | NULL           | YES  |     | NULL    |       | select,insert,update,references |         |
| yr          | int(11)     | NULL           | YES  |     | NULL    |       | select,insert,update,references |         |
| description | varchar(30) | gbk_chinese_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| saleAmount  | int(11)     | NULL           | YES  |     | NULL    |       | select,insert,update,references |         |
+-------------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
7 rows in set (0.00 sec)