【MySQL】 批量更改库，表，字段的字符集

 

库一级的更改：

-- 单个库字符集更改
ALTER DATABASE `ymcd_aisw` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
ALTER DATABASE `ymcd_aisw` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

-- 批量修改库的字符集
SELECT CONCAT('ALTER DATABASE `', `SCHEMA_NAME`, '` CHARACTER SET \'utf8\' COLLATE \'utf8_general_ci\'; ') AS `批量改库字符集SQL`
FROM information_schema.`SCHEMATA` 
WHERE `SCHEMA_NAME` NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') -- 排除系统库，可以再添加自己需要排除的库
-- `SCHEMA_NAME` IN('SASAD', 'ASDASD', ...) -- 或者指定哪些库要改

 

表一级的更改：

-- 批量改表的字符集
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHARACTER SET = utf8, COLLATE = utf8_general_ci;')
FROM information_schema.`TABLES`
WHERE `TABLE_SCHEMA` = 'ymcd_aisw' -- 指定单库
-- `TABLE_SCHEMA` IN ('x111', 'xaaaq') -- 指定多库

 

字段一级的更改：

-- 批量改字段
-- 主键字符字段不涉及索引问题，联合主键也是一样

SELECT CONCAT(
-- 指定的库和表名
'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
-- 指定的字段和数据类型
'MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
-- 指定的字符集和对应的排序规则
' CHARACTER SET utf8 COLLATE utf8_general_ci ',
-- 是否非空和默认值的组合
(CASE
    WHEN IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NULL THEN 'NOT NULL '
    WHEN IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NOT NULL THEN CONCAT('NOT NULL DEFAULT "', COLUMN_DEFAULT, '" ')
    WHEN IS_NULLABLE = 'YES' AND COLUMN_DEFAULT IS NULL THEN 'NULL DEFAULT NULL '
    WHEN IS_NULLABLE = 'YES' AND COLUMN_DEFAULT IS NOT NULL THEN CONCAT('NULL DEFAULT "', COLUMN_DEFAULT, '" ')
END),
-- 保留原始注释信息
'COMMENT "', COLUMN_COMMENT, '";'
)
FROM information_schema.`COLUMNS`
WHERE
    CHARACTER_SET_NAME = 'utf8mb4' -- 根据原字符集来筛选
    AND `TABLE_SCHEMA` = 'ymcd_aisw' -- 指定单库
    -- `TABLE_SCHEMA` IN('') -- 指定多库

 

 

简化版本：

-- 同步所有表字段字符集信息
SELECT
	TABLE_SCHEMA '数据库',
	TABLE_NAME '表',
	COLUMN_NAME '字段',
	CHARACTER_SET_NAME '原字符集',
	COLLATION_NAME '原排序规则',
	CONCAT( 'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) '修正SQL' 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = 'amerp-local' 
	AND COLLATION_NAME = 'utf8mb4_0900_ai_ci';

-- 同步所有表字符集信息
SELECT
	TABLE_SCHEMA '数据库',
	TABLE_NAME '表',
	TABLE_COLLATION '原排序规则',
	CONCAT( 'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` COLLATE=utf8mb4_general_ci;' ) '修正SQL' 
FROM
	information_schema.TABLES 
WHERE
	TABLE_COLLATION = 'utf8mb4_0900_ai_ci'
	AND TABLE_SCHEMA = 'amerp-local';