批量修改表中字段字符集和排序规则

SELECT  CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.','`', 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),
        IF(COLUMN_KEY = 'PRI', '', (CASE WHEN ISNULL(column_default)  THEN CONCAT(' ') ELSE CONCAT(' DEFAULT \'' , column_default ,'\'') 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 IN( 'rino_device','rino_system','rino_user') #要修改的数据库名称
    AND TABLE_NAME='chenfu_table'  # 要修改的指定数据表名称
    AND DATA_TYPE = 'varchar' # 要修改的指定列类型
    AND COLLATION_NAME='utf8mb4_0900_ai_ci'; # 要修改的指定字符集

结果如下

ALTER TABLE rino_device.`rino_device_user_ref` MODIFY `device_id` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  COMMENT '设备id';
ALTER TABLE rino_device.`rino_device_user_ref` MODIFY `user_id` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  COMMENT '用户id';
ALTER TABLE rino_device.`rino_device_user_ref` MODIFY `create_by` VARCHAR(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建人';

批量修改表字符集和排序规则

SELECT
CONCAT("ALTER TABLE ",TABLE_SCHEMA,".`",TABLE_NAME,
"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;")  AS target_tables 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA IN( 'rino_device','rino_system','rino_user','rino_product') # 库名
 AND TABLE_TYPE="BASE TABLE"; # 表名

执行结果如下

target_tables
ALTER TABLE rino_device.`rino_device_user_ref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE rino_system.`rino_panel_template` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE rino_device.`rino_device` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;