MySQL运维实战(5.4) MySQL元数据乱码

俊达2年前技术文章1106

表结构Comment乱码

如果DDL实际编码和character_set_client设置不一致,也会引起乱码。


$ cat test_comment_utf8.sql
create table test_comment_utf8(a int comment '列列列列列AAA') 
  comment 'BBBB表表表表表BBBB';

$ od -t x1 test_comment_utf8.sql
0000000    63  72  65  61  74  65  20  74  61  62  6c  65  20  74  65  73
0000020    74  5f  63  6f  6d  6d  65  6e  74  5f  75  74  66  38  28  61
0000040    20  69  6e  74  20  63  6f  6d  6d  65  6e  74  20  27  e5  88
0000060    97  e5  88  97  e5  88  97  e5  88  97  e5  88  97  41  41  41
0000100    27  29  20  63  6f  6d  6d  65  6e  74  20  27  42  42  42  42
0000120    e8  a1  a8  e8  a1  a8  e8  a1  a8  e8  a1  a8  e8  a1  a8  42
0000140    42  42  42  27  3b  0a
0000146


上述SQL文件以UTF8编码( “列”的utf8编码为e5  88  97,“表”的utf8编码为e8  a1  a8)

将character_set_client设置为gbk,执行上述SQL:

mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table test_comment_utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> source test_comment_utf8.sql;
Query OK, 0 rows affected (0.00 sec)


表和字段的注释出现了乱码,元数据存储的数据也不对:

mysql> show create table test_comment_utf8\G
*************************** 1. row ***************************
       Table: test_comment_utf8
Create Table: CREATE TABLE `test_comment_utf8` (
  `a` int DEFAULT NULL COMMENT '鍒楀垪鍒楀垪鍒桝AA'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
COMMENT='BBBB琛ㄨ〃琛ㄨ〃琛˙BBB'
1 row in set (0.00 sec)

mysql> select table_name, table_comment, hex(table_comment) from information_schema.tables where table_name = 'test_comment_utf8';
+-------------------+-------------------------+--------------------------------------------------------------+
| TABLE_NAME        | TABLE_COMMENT           | hex(table_comment)                                           |
+-------------------+-------------------------+--------------------------------------------------------------+
| test_comment_utf8 | BBBB琛ㄨ〃琛ㄨ〃琛˙BBB | 42424242E7909BE384A8E38083E7909BE384A8E38083E7909BCB99424242 |
+-------------------+-------------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select column_name, column_comment, hex(column_comment) from information_schema.columns where table_name = 'test_comment_utf8';
+-------------+--------------------+------------------------------------------------------+
| COLUMN_NAME | COLUMN_COMMENT     | hex(column_comment)                                  |
+-------------+--------------------+------------------------------------------------------+
| a           | 鍒楀垪鍒楀垪鍒桝AA | E98D92E6A580E59EAAE98D92E6A580E59EAAE98D92E6A19D4141 |
+-------------+--------------------+------------------------------------------------------+
1 row in set (0.01 sec)



utf8终端查看表结构

mysql> set names 'utf8';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show create table test_comment_utf8\G
*************************** 1. row ***************************
       Table: test_comment_utf8
Create Table: CREATE TABLE `test_comment_utf8` (
  `a` int DEFAULT NULL COMMENT '鍒楀垪鍒楀垪鍒桝AA'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
COMMENT='BBBB琛ㄨ〃琛ㄨ〃琛˙BBB'
1 row in set (0.00 sec)

mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test_comment_utf8\G
*************************** 1. row ***************************
       Table: test_comment_utf8
Create Table: CREATE TABLE `test_comment_utf8` (
  `a` int DEFAULT NULL COMMENT '列列列列列AAA'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
COMMENT='BBBB表表表表表BBBB'
1 row in set (0.00 sec)


gbk终端查看数据

mysql> set names 'utf8';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show create table test_comment_utf8\G
*************************** 1. row ***************************
       Table: test_comment_utf8
Create Table: CREATE TABLE `test_comment_utf8` (
  `a` int DEFAULT NULL COMMENT '閸掓鍨崚妤鍨崚妗滱A'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
COMMENT='BBBB鐞涖劏銆冪悰銊ㄣ冪悰藱BBB'
1 row in set (0.00 sec)

mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test_comment_utf8\G
*************************** 1. row ***************************
       Table: test_comment_utf8
Create Table: CREATE TABLE `test_comment_utf8` (
  `a` int DEFAULT NULL COMMENT '鍒楀垪鍒楀垪鍒桝AA'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
COMMENT='BBBB琛ㄨ〃琛ㄨ〃琛˙BBB'
1 row in set (0.00 sec)



总结

建表时字符集相关参数设置不对,可能会导致表的元数据(如表名、列名、表和列的注释、存储过程中的字符)产生乱码。




相关文章

MySQL运维实战(5.1) 字符和编码的基本概念

MySQL运维实战(5.1) 字符和编码的基本概念

字符和编码字符字符是符号,是人们用于交流的各类符号,如26个英文字母、汉字、标点符号、数学运算符、其他语言的字母和符号。编码编码是计算机中以二进制方式存储字符的方式。字符集字符集是字符和编码的映射表。...

 MySQL运维实战(1.2)安装部署:使用二进制安装部署

MySQL运维实战(1.2)安装部署:使用二进制安装部署

一般在生产环境,我们会使用二进制安装的方式安装MySQL。使用二进制安装,在处理单机多实例、升级MySQL等场景下更加方便。如果有特殊的需求(比如要打一些patch),我们还可以自己编译二进制。1、下...

MySQL运维实战(5.3) MySQL数据乱码的一些情况

MySQL运维实战(5.3) MySQL数据乱码的一些情况

表数据乱码当数据的真实编码和相关参数(常见的包括character_set_client, character_set_result, 字段的编码,终端的编码)不一致时,会产生乱码。测试1 - 表中的...

MySQL运维实战之ProxySQL(9.2)ProxySQL安装和配置

proxysql安装proxysql提供了各个linux发行版的安装包,我们可以使用操作系统的包管理系统来安装proxysql。这里我们以CentOS 7为例:1、从github下载安装包根据OS版本...

MySQL运维实战之ProxySQL(9.5)proxysql和MySQL Group Replication配合使用

如果后端MySQL使用了Group Replication,可通过配置mysql_group_replication_hostgroups表来实现高可用mysql_group_replication_...

MySQL运维实战之ProxySQL(9.4)proxysql和后端MySQL自动切换

MySQL运维实战之ProxySQL(9.4)proxysql和后端MySQL自动切换

如上图架构,当后端MySQL主库出现问题,发生主备切换后,如何自动将ProxySQL的读写切换到新的主库上?可以通过mysql_replication_hostgroups表配置实现:insert&n...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。