mysql批量修改utf8mb3为utf8mb4字符集
设置数据库字段集
ALTER DATABASE <DATABASE_NAME> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
查询utf8mb3 字符的表,并生成修改sql语句
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS sql_statements
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<database_name>'
AND TABLE_TYPE = 'BASE TABLE';
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY ', COLUMN_NAME, ' ', DATA_TYPE, IF(ISNULL(CHARACTER_MAXIMUM_LENGTH), '', CONCAT('(', CHARACTER_MAXIMUM_LENGTH, ')')), ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci', IF(IS_NULLABLE = 'NO', ' NOT NULL', ''), ';') AS sql_statements
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '<database_name>'
AND CHARACTER_SET_NAME = 'utf8mb3';
查询结果类似如下:
ALTER TABLE <database_name>.active CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE <database_name>.article CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
...
查询结果copy 到sql 控制台,执行结果类似如下结果:
Name
Value
Queries
20
Updated Rows
822
Execute time (ms)
2881
Fetch time (ms)
0
Total time (ms)
2881
Start time
2023-07-17 14:58:42.897
Finish time
2023-07-17 14:58:45.788
Last updated