简便的批量修改MySQL表的字符集和排序规则

原由：项目中的MySQL数据库、表、字段大部分的排序规则(collation)使用的都是以"_ci"结尾的，“ci”是“case insensitive”的缩写，即“大小写不敏感”，而项目的后台统计需求涉及的字符串等值查询、分组（GROUP BY）、排序（ORDER BY）等都要求需要区分“大小写”、以及“重音字符”，所以需要把所有的数据库、数据库的表、以及数据库的表的存储字符串的相关字段的排序规则统一修改为以“_bin”结尾，也就是区分大小写以及重音。

---

假定：

1.         数据库级别的字符集统一改为：“utf8mb4”，排序规则统一改为：“utf8mb4_bin”。
2.         表级别的字符集统一改为：“utf8mb4”，排序规则统一改为：“utf8mb4_bin”。
3.         字段级别的字符集保持不变，排序规则统一改为以“_bin”结尾。

---

 一：首先，批量修改所有数据库的字符集和排序规则：

SELECT CONCAT('ALTER DATABASE ',SCHEMA_NAME,' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') as 'Fanrncho' FROM information_schema.`SCHEMATA` WHERE DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8mb4' AND SCHEMA_NAME NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys');

执行上面的SQL语句后，结果如下：

 

 将上面的“Result 1”标签页的所有“ALTER DATABASE ...”格式的SQL语句拷贝出来执行一遍，就完成了第1步。

二：其次，批量修改所有数据库的表的字符集和排序规则：

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') AS Fanrncho FROM information_schema.`TABLES` WHERE TABLE_SCHEMA IN (SELECT SCHEMA_NAME FROM information_schema.`SCHEMATA` WHERE DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8mb4' AND SCHEMA_NAME NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys')) AND TABLE_TYPE="BASE TABLE";

执行上面的SQL语句后，结果如下：

 将上面的“Result 1”标签页的所有“ALTER TABLE ...”格式的SQL语句拷贝出来执行一遍，就完成了第2步。

三：最后，批量修改数据库的表的字段的排序规则：

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET ascii COLLATE ascii_bin', ' ', IF(IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), IF(ISNULL(COLUMN_DEFAULT), '', CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\'')), IF(ISNULL(COLUMN_COMMENT), '', CONCAT(' COMMENT \'', COLUMN_COMMENT, '\';'))) AS 'Fanrncho' FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA IN (SELECT SCHEMA_NAME FROM information_schema.`SCHEMATA` WHERE DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8mb4' AND SCHEMA_NAME NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys')) and COLLATION_NAME RLIKE 'ascii';

执行上面的SQL语句后，结果如下：

 将上面的“Result 1”标签页的所有“ALTER TABLE ...”格式的SQL语句拷贝出来执行一遍，就完成了把所有以“ascii”作为字符集的字段的所有排序规则改为了“ascii_bin”。如果数据库的表里还有诸如“utf8”、“utf8mb4”、或者“latin1”等字符集的排序规则不是以“_bin”结尾的需要修改，那么你只需要把上面的SQL语句里的三处“ascii”统一改为“utf8”、“utf8mb4”、或者“latin1”等，然后重复上面的后续流程即可。最终，完成第3步。

---