mysql批量更改编码，批量修改外等

最新推荐文章于 2023-08-15 22:19:20 发布

huibinwei

最新推荐文章于 2023-08-15 22:19:20 发布

阅读量786

点赞数

分类专栏： sql 文章标签：批量mysql更改编码批量修改

本文链接：https://blog.csdn.net/whb3299065/article/details/100538561

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

今天在搞数据库的时候犯了一个错误，就是同步线上库的时候发生了很多找不到字符串编码的错误，于是有了今天的文章

首先获取更改字段的语句

在查询的时候并没有找到如何批量修改，所以我的想法是通过sql语句生成修改的语句，核心就是CONCAT()函数和information_schema库。

concat()函数用于字符串的拼接，用该函数拼接处更改字段的sql 语句
information_schema库是用于存放所有库，所有表，所有字段的信息的

一条正常的数据库修改类型编码的语句

ALTER TABLE `表名` MODIFY `字段名` CHAR(32) CHARACTER SET UTF8mb4 COLLATE utf8mb4_general_ci;

再来一个他爸爸(即生产出这条sql的sql语句)

实现功能是查询该表中所有编码排序为utf8mb4_0900_as_cs类型的字段，然后改为utf8mb4编码排序规则为utf8mb4_general_ci
修改所有被*包围的地方

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET **UTF8mb4** COLLATE **utf8mb4_general_ci**',  ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA='**表名**' AND (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='text'OR DATA_TYPE='longtext') AND COLLATION_NAME='**utf8mb4_0900_as_cs**';

如果没有外键的话基本这样就可以了

有外键的情况，需要先删除外键，然后更改编码，最后还原外键

注意: 外键不能修改，所有修改方式也是删除后重新插入

1、首先按照上面的内容先将sql执行出来，并保存起来

2、要获取一下所有外键的插入信息(千万要先获取插入外键的sql，先删除的话就改跑路了)

一条正常的插入外键的sql

ALTER TABLE 表名 ADD CONSTRAINT FK_外键名 FOREIGN KEY(本表中外键id) REFERENCES 关联表表名(关联字段) ON DELETE RESTRICT ON UPDATE CASCADE;

再来一个他爸爸

SELECT CONCAT("alter table " ,table_name, " add constraint " ,constraint_name," foreign key(",COLUMN_NAME ,") references ",REFERENCED_TABLE_NAME,"(",REFERENCED_COLUMN_NAME,") on delete restrict on update CASCADE;")
FROM information_schema.`KEY_COLUMN_USAGE` 
WHERE TABLE_SCHEMA="*表名*" AND CONSTRAINT_NAME!="PRIMARY"

然后把生产出来的sql保存起来

3、删除掉原有外键

同样的流程，我挨个贴出来

一条正常的插入外键的sql

ALTER TABLE 表名 DROP FOREIGN KEY FK_外键名;

再来一个他爸爸

SELECT CONCAT("alter table " ,table_name, " DROP FOREIGN KEY " ,constraint_name,";")
FROM information_schema.`KEY_COLUMN_USAGE` 
WHERE TABLE_SCHEMA="**表名**" AND CONSTRAINT_NAME!="PRIMARY"

这一步生产的结果，可以直接运行

4、执行第一步中保存的sql：修改编码

5、执行第二步中保存的sql：重新生成外键

6、还有表的编码更改

在这里插入代码片

sqlyog可以直接用文本方式查看结果，复制“------------”线以下的结果即可
在这里插入图片描述
贴一个information_schema库下个表的作用并将一些常用的表加了重点
information_schema数据库表说明:
引用原文

SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。
TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
COLLATIONS表：提供了关于各字符集的对照信息。
COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
KEY_COLUMN_USAGE表：描述了具有约束的键列。包含了主外键信息
ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表