MySQL 批量修改数据库的字符集和排序规则

╰半橙微兮° 2022-11-10 03:53 161阅读 0赞

> ### 推荐阅读 ###
> 
> ##### Helm3（K8S 资源对象管理工具）视频教程：[https://edu.csdn.net/course/detail/32506][https_edu.csdn.net_course_detail_32506] #####
> 
> ##### Helm3（K8S 资源对象管理工具）博客专栏：[https://blog.csdn.net/xzk9381/category\_10895812.html][https_blog.csdn.net_xzk9381_category_10895812.html] #####

> [本文原文链接：https://blog.csdn.net/xzk9381/article/details/114872488，转载请注明出处。如有发现文章中的任何问题，欢迎评论区留言。][https_blog.csdn.net_xzk9381_article_details_114872488]

### 一、处理外键约束 ###

在处理大量数据时，可能会遇到外键约束的问题导致数据无法修改成功，所以在执行修改指令之前，可以先设置忽略外键约束，修改完成后再打开。命令如下：

SET FOREIGN_KEY_CHECKS=0;
     
    -- Insert your other SQL Queries here...
     
    SET FOREIGN_KEY_CHECKS=1;

### 二、修改数据库的字符集和排序 ###

修改某一个库的字符集和排序可以使用如下命令：

ALTER DATABASE <yourDB> CHARACTER SET <charset> COLLATE <collation>

例如修改 wiki 数据库字符集为 utf8，排序为 utf8\_bin：

ALTER DATABASE wiki CHARACTER SET utf8 COLLATE utf8_bin;

### 三、修改表的字符集和排序 ###

修改表的字符集可以使用如下命令：

SELECT CONCAT('ALTER TABLE `',  table_name, '` CHARACTER SET <charset> COLLATE <collation>;')
    FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
    WHERE C.collation_name = T.table_collation
    AND T.table_schema = '<yourDB>'
    AND
    (
        C.CHARACTER_SET_NAME != '<charset>'
        OR
        C.COLLATION_NAME != '<collation>'
    );

例如修改 wiki 数据库中的表字符集为 utf8，排序为 utf8\_bin：

SELECT CONCAT('ALTER TABLE `', table_name, '` CHARACTER SET utf8 COLLATE utf8_bin;')
    FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
    WHERE C.collation_name = T.table_collation
    AND T.table_schema = 'wiki'
    AND
    (
        C.CHARACTER_SET_NAME != 'utf8'
        OR
        C.COLLATION_NAME != 'utf8_bin'
    );

上面的命令执行完成后，会输出一些修改表的语句，将这些语句复制后在数据库中执行，即可完成对表的修改。

### 四、修改字段的字符集和排序 ###

如果数据类型是 varchar，使用如下命令修改字段的字符集和排序：

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET <charset> COLLATE <collation>', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = '<yourDB>'
    AND DATA_TYPE = 'varchar'
    AND
    (
        CHARACTER_SET_NAME != '<charset>'
        OR
        COLLATION_NAME != '<collation>'
    );

如果数据类型是非 varchar，使用如下命令修改字段的字符集和排序：

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET <charset> COLLATE <collation>', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = '<yourDB>'
    AND DATA_TYPE != 'varchar'
    AND
    (
        CHARACTER_SET_NAME != '<charset>'
        OR
        COLLATION_NAME != '<collation>'
    );

上面的 sql 执行完成后会输出修改修改字段的 sql，将这些 sql 复制到 mysql 中执行即可完成对字段的修改。

[https_edu.csdn.net_course_detail_32506]: https://edu.csdn.net/course/detail/32506
[https_blog.csdn.net_xzk9381_category_10895812.html]: https://blog.csdn.net/xzk9381/category_10895812.html
[https_blog.csdn.net_xzk9381_article_details_114872488]: https://blog.csdn.net/xzk9381/article/details/114872488