mysql修改表编码为utf8mb4的方法
注意:为避免失误,请先备份你的数据库!!!
一般情况下,我们会设置MySQL默认的字符编码为utf8,但是近些年来,emoji表情的火爆使用,给数据库带来了意外的错误,就是emoji的字符集已经超出了utf8的编码范畴
现在的应用一般不要在MySQL上使用utf8字符编码,推荐使用utf8mb4
utf8mb4是MySQL5.5.3版本之后支持的字符集,so,如果你需要使用这个字符集,前提条件是你的MySQL版本必须 >= 5.5.3
#查看当前系统默认的字符集设置
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
#修改database默认的字符集
ALTER DATABASE {database_name} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
#可以看到,虽然修改了database的字符集为utf8mb4,但是实际只是修改了database新创建的表,默认使用utf8mb4,原来已经存在的表,字符集并没有跟着改变,需要手动为每张表设置字符集
#只修改表默认的字符集
ALTER TABLE {table_name} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
#修改表默认的字符集和所有字符列的字符集
ALTER TABLE {table_name} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
#修改column默认的字符集
ALTER TABLE {table_name} CHANGE {column_name} {column_name} VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
检查字段的最大长度和索引列
字段长度
由于从utf8升级到了utf8mb4,一个字符所占用的空间也由3个字节增长到4个字节,但是我们当初创建表时,设置的字段类型以及最大的长度没有改变。例如,你在utf8下设置某一字段的类型为TINYTEXT, 这中字段类型最大可以容纳255字节,三个字节一个字符的情况下可以容纳85个字符,四个字节一个字符的情况下只能容纳63个字符,如果原表中的这个字段的值有一个或多个超过了63个字符,那么转换成utf8mb4字符编码时将转换失败,你必须先将TINYTEXT更改为TEXT等更高容量的类型之后才能继续转换字符编码
索引
在InnoDB引擎中,最大的索引长度为767字节,三个字节一个字符的情况下,索引列的字符长度最大可以达到255,四个字节一个字符的情况下,索引的字符长度最大只能到191。如果你已经存在的表中的索引列的类型为VARCHAR(255)那么转换utf8mb4时同样会转换失败。你需要先将VARCHAR(255)更改为VARCHAR(191)才能继续转换字符编码
修改配置文件
vim /etc/my.cnf
# 对本地的mysql客户端的配置
[client]
default-character-set=utf8mb4
# 对其他远程连接的mysql客户端的配置
[mysql]
default-character-set=utf8mb4
# 本地mysql服务的配置
[mysqld]
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
> service mysqld restart
检查修改
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
修复&优化所有数据表
mysqlcheck -u root -p --auto-repair --optimize --all-databases