mysql数据库、表、字段转换编码

最新推荐文章于 2024-07-17 09:36:08 发布

handongopqwe

最新推荐文章于 2024-07-17 09:36:08 发布

阅读量1.2k

点赞数

分类专栏： mysql 文章标签： mysql emoji 编码转换

mysql 专栏收录该内容

2 篇文章

订阅专栏

本文提供了一种使用MySQL的information_schema来生成SQL语句的方法，这些SQL语句可以用于将现有数据库和表的字符集从旧版UTF8迁移到新的UTF8MB4，并设置对应的Unicode排序规则。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

use information_schema;
SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql 
FROM `TABLES` where table_schema like "DB_NAME" group by table_schema;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `TABLES` where table_schema like "DB_NAME" group by table_schema, table_name;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "DB_NAME" and data_type in ('varchar');
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "DB_NAME" and data_type in ('text','tinytext','mediumtext','long