MySQL修改排序规则collation语句

• 有时候数据的表和字段与其他表和字段不一致，这个时候通常需要查询修改下；
• 比如utf8与utf8mb4, utf8mb4_general_ci 与 utf8_general_ci 等等。
• MySQL 5.7与8.0默认的排序规则不一致，5.7是 utf8mb4_general_ci，8.0是 utf8mb4_0900_ai_ci 如果你的数据库是从5.7升上来的，建议用旧的保持一致，如果是全新的8.0，就不需要改，最重要的是表的排序规则保持一致，不然关联查询会报 排序规则不一致；
• 下列语句不支持 ENUM的重命名，另外对于字段备注有引号的也改不了，到时候手动修改吧；
• 如果遇到大表，几百万行的，可能修改不了，到时重建表，迁移过去吧；

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  not RLIKE 'mb4' and TABLE_SCHEMA = 'aaa';

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  not  RLIKE 'utf8mb4_general_ci' and TABLE_SCHEMA = 'aaa';

SELECT 
    CONCAT('ALTER TABLE `', table_name, '` MODIFY COLUMN `', column_name, '` ', DATA_TYPE, 
        '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci', 
        (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),
        " DEFAULT ",(CASE WHEN COLUMN_DEFAULT IS NULL THEN ' NULL' 
         WHEN COLUMN_DEFAULT=""  THEN "''"  ELSE COLUMN_DEFAULT END),
        (case when IFNULL(column_comment,'')='' then '' else concat(' COMMENT \'' , column_comment ,'\'') end),
        ';') as `sql`
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME not rlike 'mb4'   
    and TABLE_SCHEMA = 'aaa' #要修改的数据库名称

SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',COLUMN_NAME '字段',CHARACTER_SET_NAME '原字符集',COLLATION_NAME '原排序规则',
    CONCAT('ALTER TABLE `', table_name, '` MODIFY COLUMN `', column_name, '` ', DATA_TYPE, 
        '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci', 
        (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),
        " DEFAULT ",(CASE WHEN COLUMN_DEFAULT IS NULL THEN ' NULL' 
         WHEN COLUMN_DEFAULT=""  THEN "''"  ELSE COLUMN_DEFAULT END),
        (case when IFNULL(column_comment,'')='' then '' else concat(' COMMENT \'' , column_comment ,'\'') end),
        ';') as `sql`
FROM information_schema.COLUMNS
WHERE COLLATion_name  not  rlike 'utf8mb4_general_ci'   
    and TABLE_SCHEMA = 'aaa' #要修改的数据库名称

• enum 例子如下,单独修改吧

ALTER TABLE `smart_pbm`.`t_config_dictionary` 
MODIFY COLUMN `cd_type` enum('other','change_amount') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'other' COMMENT '类型' AFTER `cdMemo`;