批量修改表字段排序规则

修改字段排序规则

SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME AS '表',
    COLUMN_NAME AS '字段',
    CHARACTER_SET_NAME AS '原字符集',
    COLLATION_NAME AS '原排序规则',
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS '修正SQL'
FROM
    information_schema.`COLUMNS`
WHERE
    CHARACTER_SET_NAME = 'utf8mb4' AND COLLATION_NAME != 'utf8mb4_0900_ai_ci' AND TABLE_SCHEMA = '数据库名称';

修改表字段默认规则

SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME AS '表',
    TABLE_COLLATION AS '原排序规则',
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS '修正SQL'
FROM
    information_schema.`TABLES`
WHERE
    TABLE_COLLATION IN ('utf8_general_ci','utf8mb4_general_ci') AND TABLE_SCHEMA LIKE '数据库名称%';