Myql 一次性更换字符集

前言

最近项目用了 mysql8，但是我本地还是 mysql5.7 的，然后在复制的时候出现了问题，一些表是从我本地复制过去的，字符集是 utf8mb_general_ci，而 mysql8 上的表默认是 utf8mb4_0900_ai_ci。等再次将 mysql8 中的表全量复制到我本地的时候，就发生了异常。因为本地 mysql5.7 中没有 utf8mb4_0900_ai_ci 字符集，所以就报错了。

批量更新字段字符集

光改表的字符集是不够的，你去看一下表中的字段发现字段的字符集还是原来的字符集，所以得把字段的字符集也一起改掉才行。

批量修改整个数据库的表的所有字段的 sql 如下：

SELECT
	TABLE_SCHEMA '数据库',
	TABLE_NAME '表',
	COLUMN_NAME '字段',
	CHARACTER_SET_NAME '原字符集',
	COLLATION_NAME '原排序规则',
	CONCAT(
	'ALTER TABLE ',
	TABLE_NAME,
	' MODIFY COLUMN ',
	COLUMN_NAME,
	' ',
	COLUMN_TYPE,
	-- - 设置新的编码和排序规则
	' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci',
	( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
	( CASE WHEN COLUMN_COMMENT = '' THEN ' ' ELSE concat( ' COMMENT''', COLUMN_COMMENT, '''' ) END ),
	';' 
) '修正SQL' 
FROM
	information_schema.`COLUMNS` 
WHERE
	-- -过滤正确排序规则
	COLLATION_NAME != 'utf8mb4_0900_ai_ci'
	-- -数据库名称
	AND TABLE_SCHEMA = '数据库名';

上面的 sql 会把 collation_name 不是 utf8mb4_0900_ai_ci 的筛选出来，然后批量执行一下 修正 SQL 即可。

批量修改表的字符集

批量修改表的字符集的 sql 如下：

SELECT 
CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;") 
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="user_center" # 修改为需要更改的数据库名称
AND TABLE_TYPE="BASE TABLE"

数据库的字符集和排序规则

这个用 navicate 直接改，或者使用如下 sql 语句：

ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;