如何批量修改mysql表字符集

1. 更改单张表的编码

ALTER TABLE YOUR_TABLE_NAME DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2. 更改所有表的编码

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,
              ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'YOUR_TABLE_SCHEMA';

3. 批量修改表字段字符集

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 = 'YOUR_TABLE_SCHEMA'
  AND ( DATA_TYPE = 'varchar' OR DATA_TYPE = 'char');

批量更新的时候，比如在更新工作流表的字符集的时候，会出现外键约束错误，需要额外关闭外键约束

//关闭外键约束
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE YOUR_TABLE_NAME MODIFY COLUMN COLUMN_NAME varchar(255)
	CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE YOUR_TABLE_NAME MODIFY COLUMN COLUMN_NAME varchar(255)
	CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
...	
//开启外键约束
SET FOREIGN_KEY_CHECKS = 1;