MYSQL修改数据库、表、字段字符集

背景

由于mysql低版本暴了漏洞，所以客户就升级了mysql版本。升级到了最新版8.0.30，升级最新版后，应用就再也查不出数据，恢复到最初的版本有可以查到数据。经过确认最初数据库的表字符集为utf8,升级后这些字符集自动修改为utf8mb3。新版本数据库移除了utf8字符集，而应用本身不支持utf8mb3,所以需要修改这些字符集。经过再次确认最新版mysql以及应用都支持utf8mb4,最终决定修改字符集为utf8mb4。

修改字符集

数据库

ALTER DATABASE 数据库名称 DEFAULT CHARACTER SET 编码名称 [COLLATE ...];

例如：

ALTER DATABASE activity CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

表

ALTER TABLE 表名称  CONVERT TO CHARACTER SET 编码名称 [COLLATE ...]

例如：

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

以上修改会同时变更现有数据的字符集。如果只修改表的默认字符集，而不修改现有数据的字符集可以使用以下内容

ALTER TABLE 表名称 DEFAULT CHARACTER SET 编码名称 [COLLATE...];

例如:

ALTER TABLE users DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

字段

ALTER TABLE 表名称 CHANGE 字段名称 字段名称 CHARACTER SET 编码名称 [COLLATE ...];

例如：

ALTER TABLE users CHANGE LoginName LoginName VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

批量修改数据库表字符集

第一步

使用以下脚本并执行，产生修改数据表字符集的脚本。

SELECT 
CONCAT("ALTER TABLE ",TABLE_SCHEMA,'.',TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") 
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA IN('数据库名称','数据库名称') 
AND TABLE_TYPE="BASE TABLE";

例如：

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

导出以上查询结果，执行导出的查询结果即可。如果数据量比较大，执行的时间可能会比较久。

查看字符集

查看数据库支持的字符集

select * from information_schema.character_sets;

查看数据库支持的检验规则

select * from information_schema.collations;

查看表字符集及检验规则

select * from information_schema.tables where table_name='表名' 

查看字段编码

SHOW FULL COLUMNS FROM  表名

转载请注明：清风亦平凡 » MYSQL修改数据库、表、字段字符集

喜欢 (2)or分享 (0)

赏 支付宝扫码打赏 微信打赏