锐客网

  1. 首页 > 睿知 > it技术 > >

最近遇到的一个库&表字符集大坑

IT知识字符集一个

落花踏尽游何处，笑入胡姬酒肆中。这篇文章主要讲述最近遇到的一个库&表字符集大坑相关的知识，希望能为你提供帮助。
我们生产环境的建表语句，之前一直要求研发提交时候不要带上字符集和排序集，这样就可以follow数据库默认的配置。但是最近发现掉坑里了。
至于是什么坑，为什么掉坑。可以看下面的例子

环境：
CentOS7
GreatSQL8.0.25，可以理解为Percona的加强版，增加了一些挺不错的feature，例如并行查询之类特性。
这个案例只要是mysql8的任一版本就可以。


[test]> \\s
--------------
/usr/local/mysql/bin/mysqlVer 8.0.25-15 for Linux on x86_64 (GreatSQL, Release 15, Revision c7feae175e0)

Connection id:27
Current database: test
Current user:root@localhost
SSL:Not in use
Current pager:less
Using outfile:
Using delimiter:;
Server version:8.0.25-15 GreatSQL, Release 15, Revision c7feae175e0
Protocol version: 10
Connection:Localhost via UNIX socket
Server characterset:utf8mb4
Dbcharacterset:utf8mb4
Client characterset:utf8mb4
Conn.characterset:utf8mb4
UNIX socket:/data/GreatSQL/mysql.sock
Binary data as:Hexadecimal
Uptime:15 hours 44 min 8 sec

Threads: 4Questions: 272Slow queries: 25Opens: 431Flush tables: 3Open tables: 347Queries per second avg: 0.004
--------------

-- 字符集和字符排序集
[test]> show global variables like %charac%;
+--------------------------+--------------------------------------------+
| Variable_name| Value|
+--------------------------+--------------------------------------------+
| character_set_client| utf8mb4|
| character_set_connection | utf8mb4|
| character_set_database| utf8mb4|
| character_set_filesystem | binary|
| character_set_results| utf8mb4|
| character_set_server| utf8mb4|
| character_set_system| utf8mb3|
| character_sets_dir| /usr/local/GreatSQL-8.0.25/share/charsets/ |
+--------------------------+--------------------------------------------+


[test]> show global variables like %collation%;
+-------------------------------+--------------------+
| Variable_name| Value|
+-------------------------------+--------------------+
| collation_connection| utf8mb4_0900_ai_ci |
| collation_database| utf8mb4_0900_ai_ci |
| collation_server| utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)


-- 开始创建库表
-- db1模拟的是mysql5.7升级到8.0之前，业务侧提交的建库建表语句
CREATE DATABASE db1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

use db1;

create table t ( `a` int DEFAULT NULL,
`b` varchar(100) DEFAULT NULL
) ENGINE=InnoDB;


-- db2模拟的是升级到8.0之后，业务侧提交的建库建表语句
CREATE DATABASE db2 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

use db2;

create table t ( `a` int DEFAULT NULL,
`b` varchar(100) DEFAULT NULL
) ENGINE=InnoDB;


-- 看下字符集的情况
[db2]> select
`TABLE_SCHEMA`,
`TABLE_NAME`,
`TABLE_COLLATION`,
`CREATE_TIME`
from information_schema.`TABLES`
where
`TABLE_SCHEMA` IN (db1,db2) ;

+--------------+------------+--------------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION| CREATE_TIME|
+--------------+------------+--------------------+---------------------+
| db1| t| utf8_general_ci| 2022-04-18 13:14:54 |
| db2| t| utf8mb4_0900_ai_ci | 2022-04-18 13:14:57 |
+--------------+------------+--------------------+---------------------+
2 rows in set (0.00 sec)



上面这种在生产上这回出现下面这种情况：
刚开始运行的时候，我们用的是mysql5.7，建库默认用字符集utf8 字符排序集utf8_general_ci。这里一点问题也没有。
后来，数据库版本升级到了8.0了，并且改了默认字符集为utf8mb4 字符排序集为utf8mb4_0900_ai_ci，这里也没任何问题。
新创建的数据库，不显式指定的话，也follow数据库层面的字符集和字符排序集，也就是utf8mb4 和utf8mb4_0900_ai_ci 。
但是， 如果我们在老的数据库里面创建新表的时候，如果不显式指定的话，会follow所在数据库的字符集和字符排序集的（也就是 虽然升级到8.x了，但是创建的表还是用的utf8 和utf8_general_ci），这就操蛋了啊。。
【最近遇到的一个库&表字符集大坑】

-- 按时间排序，看下最近有哪些表的字符集存在异常

SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`TABLE_COLLATION`,
`CREATE_TIME`
FROM information_schema.`TABLES`
WHERE
`TABLE_SCHEMA` NOT IN (sys,mysql,information_schema,performance_schema)
AND
TABLE_COLLATION=utf8_general_ci
ORDER BY CREATE_TIME DESC
LIMIT 10


这一堆的表，咋改进呢，挺费事的，如果你直接执行  alter database xxx DEFAULT CHARACTER SET utf8mb4; 这样风险很高的，基本上停服搞了，如果库下面的表有事务还没提交，这个alter database一直是pending的。


那么， 只能从其它方面下手了：
1、严格控制DDL语句，新的库和表必须显式定义  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
2、对于已经是utf8编码的的表 ，暂时不去动它
3、找个时间窗口，把表全部转为  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci  （工作量不小。。）




tips，字符集问题带来的索引不生效的案例：
[test]> alter table db1.t add index idx_b(b);
[test]> alter table db2.t add index idx_b(b);

[test]> select * from db1.t;
+------+------+
| a| b|
+------+------+
|1 | abc|
+------+------+
1 row in set (0.00 sec)

[test]> select *

    推荐阅读


    上一篇：golang替换无法显示的特殊字符u0000, 00, ^@

    下一篇：docker安装portainer详细步骤