如何批量修改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;

来源地址：https://blog.csdn.net/goodcto/article/details/128778490