以库名benchint_medicare为例，将这个库所有的字符集不一致的表找出来，生成修改语句，执行即可
-- 查看当前数据库默认设置
SELECT
@@collation_server AS '服务器排序规则',
@@collation_database AS '当前数据库排序规则',
SCHEMA_NAME AS '数据库名',
DEFAULT_COLLATION_NAME AS '数据库默认排序规则'
FROM
INFORMATION_SCHEMA.SCHEMATA
WHERE
SCHEMA_NAME = DATABASE();
-- 设置一个固定的字符集和排序规则 utf8mb4_0900_ai_ci
SET @character_set_database = 'utf8mb4';
SET @collation_database = 'utf8mb4_0900_ai_ci';
-- 修改所有数据库的字符集和排序规则
SELECT
SCHEMA_NAME,
CONCAT(
'ALTER DATABASE ',SCHEMA_NAME,' CHARACTER SET ',@character_set_database,' COLLATE ',@collation_database,';'
) AS alter_statement
FROM information_schema.SCHEMATA t
WHERE SCHEMA_NAME NOT IN ('information_schema','sys','mysql','performance_schema')
AND SCHEMA_NAME in ('benchint_medicare')
;
-- 更新所有表的字符集和字符集排序规则和数据库的保持一致 耗时较长
-- 注意在执行之前加上 SET FOREIGN_KEY_CHECKS = 0; -- 不检查外键关系
SELECT
TABLE_SCHEMA,TABLE_NAME, TABLE_COLLATION ,@collation_database,
CONCAT('
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE ',table_schema,'.', table_name, ' CONVERT TO CHARACTER SET ',@character_set_database,' COLLATE ',@collation_database,';')
FROM information_schema.tables t
WHERE
1=1
AND t.TABLE_COLLATION != @collation_database
AND t.TABLE_TYPE != 'VIEW'
AND t.ENGINE != 'FEDERATED'
AND t.table_schema in ('benchint_medicare')
AND t.table_schema not in ('information_schema','sys','mysql','performance_schema')
AND t.TABLE_COLLATION IS NOT NULL;
川公网安备 51010702003150号
留下您的脚步
最近评论