在 MySLQ
中 UPDATA 和 INSERT 数据的时候,如果数据中带有 emoji
图标,例如:💗
、👽
、💔
以及类似 ℃
这种字符时大概率的情况下更新或者插入是不会成功的,同时会报如下错误。
Error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD:
Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F...' for column 'name' at row
阅读文章的过程中如果有任何疑问,欢迎添加笔者为好友,拉您进微信交流群,一起交流技术,一起打造高质量的职场技术交流圈子,抱团取暖,共同进步。
作为开发人员,每每遇到此类问题的时候,免不了都会把以上错误到百度、google 中搜搜一翻或者直接把该错误截图给DBA解决...
其实这也怪不得开发人员,因为这个究其原因是 MySQL 在 5.5 版本之前,MySQL 中的 UTF-8 编码只支持 BMP 这部分的 Unicode 编码区(即只支持 1-3个字节的编码),而 emoji 图标恰好是需要 4 个字节编码存储的。因此出现该错误是在所难免的。
好在从 MySQL 5.5 开始支持 4 个字节 UTF 编码了,为了和已经使用的 UTF-8 编码进行区分,4 个字节的 UTF-8 编码的字符集名称为 utf8mb4 。即一个字符最多有 4 字节,所以能支持更多的字符集。所以要解决前面提出的问题,必需把数据库表字符编码全部改成 utf8mb4 。
常用字符集
- ASCII:美国信息互换标准编码;英语和其他西欧语言;单字节编码,7 位表示一个字符,共 128 字符。
- GBK:双字节,汉字内码扩展规范;中日韩汉字、英文、数字;双字节编码;共收录了 21003 个汉字,GB2312 的扩展。
- UTF-8:Unicode 标准的可变长度字符编码;Unicode标准(统一码),业界统一标准,包括世界上数十种文字的系统;
- UTF-8:使用一至三个字节为每个字符编码。
- utf8mb4:存储四个字节,应用场景用于存储
emoji
表情,因为可以emoji
表情四个字节。 - utf8mb4:MySQL版本 > 5.5.3 。
- 其他常见字符集:
UTF-32
,UTF-16
,Big5
,latin1
数据库中的字符集包含两层含义
- 各种文字和符号的集合,包括各国家文字、标点符号、图形符号、数字等。
- 字符的编码方式,即二进制数据与字符的映射规则。
MySQL 修改编码为 utf8mb4 编码的操作方式如下:
确认版本
mysql --version
首先备份
升级数据之前备份您服务器上的所有数据,保持良好习惯,安全第一!
升级您的 MySQL
如果 MySQL 数据库版本低于 5.5.3 版本的,请先升级你的 MySQL 版本到 5.5.3+ 的版本。
这里需要提醒一句,由于 MySQL 5.5 以后的版本和 MySQL 5.5 之前的版本存在一些版本兼容问题,因此低版本的 MySQL 升级到最新的 MySQL 版本是有风险的。
MySQL 新版本可以在这里下载Upgrade the MySQL server to v5.5.3+。
修改您的数据库、表、字段
# 对每一个数据库:
ALTER DATABASE 这里数据库名字 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 对每一个表:
ALTER TABLE 这里是表名字 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 对每一个字段:
ALTER TABLE 这里是表名字 CHANGE 字段名字 重复字段名字 VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 上面一句或者使用 modify 来更改
ALTER TABLE 这里是表名字 modify 字段名字 VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '';
utf8mb4 编码完全兼容 utf8 编码,不会存在乱码或其他数据丢失的形式出现。理论上是可以放心修改,如果您不放心修改,您可以拿备份恢复数据。
检查你的字段和索引
不要将所有的都设置成 utf8mb4
,这个没有必要。建议只在需要把某些字段设置成 utf8mb4
时再设置。
修改 MySQL 配置文件
注意如果你在 /etc/
目录下找不到 /etc/my.cnf
文件,可以按照如下方式进行操作:
# 进入 support-files 目录
cd /usr/local/mysql/support-files/
# 将这个文件复制到 `/etc/` 目录中并将名字命名为`my.cnf`
sudo cp my-default.cnf /etc/my.cnf
# 然后编辑`my.cnf`文件,将下面内容复制到里面。
sudo vim /etc/my.cnf
MySQL configuration file (/etc/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
重启 MySQL
重启步骤很重要,不然没有任何效果。
Mac 中可以按如下方式操作:
- 启动:
sudo /usr/local/mysql/support-files/mysql.server start
- 停止:
sudo /usr/local/mysql/support-files/mysql.server stop
- 重启:
sudo /usr/local/mysql/support-files/mysql.server restart
其实可以使用 mysql.server start
简单的方式启动服务。
Linux 系统中可以以这种形式重启、停止或启动
service mysqld stop
service mysqld start
service mysqld restart
Windows 系统重启、停止操作如下
图形化操作方式
我的电脑/此电脑 ->(右键)管理 -> 服务与应用程序 -> 服务 -> MySQL -> 启动(停止、暂停、恢复);
命令行方式
- 点击 开始 -> 运行 或使用 快捷键 Win+R。
- 启动:输入 net start mysql
- 停止:输入 net stop mysql
查看是否设置成功
通过下面命令查询是否设置成功!
# 登陆MySQL进行查询
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
# 运行上面代码显示下面结果
# +--------------------------+--------------------+
# | Variable_name | Value |
# +--------------------------+--------------------+
# | character_set_client | utf8mb4 |
# | character_set_connection | utf8mb4 |
# | character_set_database | utf8mb4 |
# | character_set_filesystem | binary |
# | character_set_results | utf8mb4 |
# | character_set_server | utf8mb4 |
# | character_set_system | utf8 |
# | collation_connection | utf8mb4_unicode_ci |
# | collation_database | utf8mb4_unicode_ci |
# | collation_server | utf8mb4_unicode_ci |
# +--------------------------+--------------------+
# 查看表的情况
mysql> SHOW FULL COLUMNS FROM users_profile;
到这一步表示你成功了!恭喜你!~
修复和优化表
我跑到这一步其实没有任何必要修复和优化表,为了保险起见。
REPAIR TABLE 表名字;
OPTIMIZE TABLE 表名字;
------完------
推荐阅读:
更多学习讨论欢迎关注公众号: