MySQL字符集修改

修改数据库字符集

ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

生成批量修改表及表字符字段的字符集

SELECT alter_sql FROM (
/** 通过匹配表字符集查询需要修改的表字符集 */
SELECT CONCAT('ALTER TABLE ', table_name, 
  ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_COLLATION != 'utf8mb4_general_ci'
UNION ALL
/** 通过匹配字段字符集查询需要修改的表字符集 */
SELECT CONCAT('ALTER TABLE ', table_name, 
  ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS alter_sql
FROM information_schema.`COLUMNS` 
WHERE TABLE_SCHEMA = 'database_name' AND character_maximum_length IS NOT NULL 
  AND COLLATION_NAME != 'utf8mb4_general_ci') table_name
GROUP BY alter_sql;

 修改表及表字符字段的字符集

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 
  COLLATE utf8mb4_general_ci;