生成修改表排序规则的SQL语句
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,
' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') '修正SQL'
from information_schema.tables
where TABLE_SCHEMA = '数据库名'
and TABLE_COLLATION = 'utf8mb4_0900_ai_ci';
生成的 SQL 语句如下：
ALTER TABLE 数据库名.tb_batch CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE 数据库名.tb_batch_scale CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
复制执行即可；
生成修改字段排序规则的SQL语句
SELECT
CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE,
'(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
(CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),
(case when IFNULL(column_comment,'')='' then '' else concat(' COMMENT \'' , column_comment ,'\'') end),
';') as `修正SQL`
FROM information_schema.COLUMNS
WHERE 1=1
and TABLE_SCHEMA = '数据库名' #要修改的数据库名称
and DATA_TYPE = 'varchar'
and COLLATION_NAME='utf8mb4_0900_ai_ci'
生成的 SQL 语句如下：
ALTER TABLE `tb_categories` MODIFY `code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '标识';
ALTER TABLE `tb_categories` MODIFY `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称';
复制执行即可；