MySQL 批量修改数据表编码及字符集

当需要修改数据库编码和字符集时,通常需要对其下属的所有表及表中所有字段进行修改。以下备注批量修改方案( 以修改为 utf8mb4_bin 为例,注意将 db_name 换为真实的数据库名 )。

1. 修改数据库编码及字符集

这一步比较简单,直接执行即可:

ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;

2. 修改数据表与表中字段的编码及字符集

这里需要两步。

首先,需要利用语句,生成所有实际执行的语句:

SELECT 
CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;") 
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="db_name"
AND TABLE_TYPE="BASE TABLE"

此语句会基于 MySQL 的元数据表,得到一组可直接执行的 SQL 列表,如下:

ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table3` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table4` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table5` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table6` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

其中,table1table6 即为数据库中的所有数据表。

然后,直接将语句粘贴并执行即可。

注意,这里使用 CONVERT TO 而非 DEFAULT,是因为后者不会修改表中字段的编码和字符集。

此外,对于数据表比较多的数据库,可以先将第一步的执行结果导出到 .sql 文件,再通过该 SQL 文件执行即可。

参考链接

  1. mysql - Difference between database, table, column collation - Stack Overflow
  2. automation - Alter charset and collation in all columns in all tables in MySQL - Server Fault
  3. character encoding - How to convert an entire MySQL database characterset and collation to UTF-8? - Stack Overflow
  4. mysql - How to convert all tables in database to one collation? - Stack Overflow
  5. How to easily convert utf8 tables to utf8mb4 in MySQL 5.5 - Database Administrators Stack Exchange
  6. MySQL case insensitive select - Stack Overflow
  7. Character Set and Collation Overview - MariaDB Knowledge Base

DB.Reid
KEEP INNOCENT

I wanna.

1.1k 声望
72 粉丝
0 条评论
推荐阅读
WeCenter 迁移踩坑备忘
之前已经考虑到 WeCenter 会将上传的文件存储在本地磁盘,故而直接将整个服务的文件夹打包迁移,( 同时迁移数据库 )。在配置好了 Nginx 后,打开 URL,发现出现如下报错:

dailybird阅读 2.5k

一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山7阅读 1.6k评论 2

分布式高可用Mysql数据库Percona XtraDB Cluster 8.0 与 Proxysql 史上最详尽用法指南
PXC是Percona XtraDB Cluster的缩写,是 Percona 公司出品的免费MySQL集群产品。PXC的作用是通过mysql自带的Galera集群技术,将不同的mysql实例连接起来,实现多主集群。在PXC集群中每个mysql节点都是可读可写的...

apollo0084阅读 7.2k评论 2

MongoDB 插入时间与更新时间(create_time/update_time)
MongoDB 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time,自动更新 update_time

qbit阅读 13.9k评论 2

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 703

封面图
SegmentFault 思否技术周刊 Vol.70 — 深入 MySQL 实战
MySQL 软件采用了 GPL( GNU 通用公共许可证),由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。

Beverly2阅读 1.5k

封面图
MySQL 数据库索引技术原理初探
一本书 500 页的书,如果没有目录,直接去找某个知识点,可能需要找一会儿,但是借助前面的目录,就可以快速找到对应知识点在书的哪一页。这里的目录就是索引。

mylxsw1阅读 1.2k

I wanna.

1.1k 声望
72 粉丝
宣传栏