MySql修改全部表和字段编码

修改全部表

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS sql_statements
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '数据库名称'

返回的下面这种SQL,然后批量执行即可

ALTER TABLE gen_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改全部字段

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' MODIFY ', COLUMN_NAME, ' ', column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS sql_statements
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '数据库名称'
AND DATA_TYPE IN ('char','varchar')

返回的下面这种SQL,然后批量执行即可

ALTER TABLE gen_table MODIFY table_name varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;