修改mysql数据库字符集:从utf8转为utf8mb4

监督编程的板栗半夜醒了起来喝杯茶,想起网站内容还没支持emoji表情(比如😃),于是打开电脑折腾了一下。因为对数据库操作得不多,查了一些博客,还是捣鼓了好一会才解决了这个问题,在此记录一下。

看到我开电脑,板栗兴奋地跳到桌上来监督,瞪着他的大眼睛。(右边的小肥猫名字叫”板栗“,他两岁了!😝)

数据库不支持emoji的原因是编码问题,因为我Mysql数据库里的默认字符集是utf8,utf8最多支持存储3字节的字符,存个字母、普通的汉字都是没问题的,但是存emoji表情字符需要占用4个字节,所以utf8编码就处理不了了。mysql扩展了一个新的字符集utf8mb4来解决这个问题,utf8mb4是utf8的超集,最长可以用4个字节来存储一个字符,当然也可以存emoji表情,以及一些不常用的汉字等。

一、备份数据,导出sql文件

因为是在现有数据库上进行修改,所以先备份一下。可以使用mysqldump指令轻松备份备份Mysql数据库,其原理是读取数据库,将数据库的表结构、数据导出为sql语句,这些sql语句中包含了数据库里所有的内容,可以在新的数据库重建数据表、恢复数据等,这种导出sql语句的备份方式也称为逻辑备份。数据库还有另外的备份方式——物理备份,即直接复制原始文件进行备份,当然这里没必要纠结,直接逻辑备份就好。

这里分两步导出,表结构和数据分别导出。因为我们要更改字符集,而建表sql语句中包含了字符集信息,所以我们把表结构和数据分开导出,方便全局修改表的字符集。

1. 导出表结构

mysqldump -u root -p -d awesome > createdb.sql

  • -u root -p 表示使用root用户连接mysql,通过密码验证
  • -d表示mysqldump命令不创建INSERT语句,即不导出数据
  • awesome是我的数据库名,表结构sql导出到了createdb.sql文件中

此时需要修改createdb.sql文件中建表语句中的字符集,打开文件,将其中的utf8替换为utf8mb4,vim下可以用指令 :%s/utf8/utf8mb4/g 一键替换:

2. 导出数据

mysqldump -u root -p --quick --no-create-info --extended-insert awesome > data.sql 
  • --quick 这个选项使得MySQL不会把整个导出的内容读入内存再执行导出,而是在读到的时候就写入导文件中
  • --no-create-info 不要创建表结构的sql,和上一步骤互补的
  • --extended-insert 表示长INSERT,多row在一起批量INSERT,提高导入效率
导出操作完成后,数据备份就完成了,使用createdb.sql和data.sql这两个文件的sql语句就可以将整个数据库恢复了。

二、修改mysql默认字符集

修改mysql的配置文件(通常是/etc/mysql/my.cnf):

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

如果你的配置文件不在/etc/mysql目录,可以尝试使用指令locate my.cnf查找。

三、重建数据库并导入数据

1. 重建数据库

DROP DATABASE awesome;
CREATE DATABASE awesome DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

这一步需要谨慎!第一句先删除掉原有数据库,最好先使用第二条指令创建一个新的库进行测试,以确保备份的数据能正常恢复,awesome换成你自己的库名。

2. 创建库表并导入数据

mysql -u root -p awesome < createdb.sql​
mysql -u root -p awesome < data.sql​

至此,经过备份、修改字符集、重建数据库等步骤终于完成了数据库字符集的修改。此外使用ALTER指令可以分别修改数据库、表、字段的字符集,但是因为字段、表太多而略显繁琐。

咦,板栗好像睡着了🙄!

监督编程时睡着的板栗

嘿——醒醒,醒醒~

¥赞赏