让 MySQL 支持表情字符 Emoji

随着智能手机的普及,大家对于各种即时通信工具也是越来越依赖了。那么 Emoji 这个东西也被越来越广泛的采用。Emoji 是日本人发明的,表示 “picture(e)” + “character(moji)”。在 2010 年 Unicode 6.0 版本发布的时候,已经纳入了几百个 Emoji 字符。Emoji 字符在 UTF-8 编码体系中,是使用 4 个字节来编码的。所以,对于编码设定为 UTF-8 的 MySQL 数据库是无法正确存储 Emoji 字符的(准确的说,是无法存储超过 3 字节的 UTF-8 编码的字符),你可能会看到这样的错误信息:

在 MySQL 5.5.3 之前的版本中,所谓的 utf8 编码只能支持最多 3 个字节的 UTF-8 编码的字符,实在无法理解一个面向全球的数据库产品,为什么不能对 UTF-8 编码进行完整的支持?那么在 MySQL 的 5.5.3 及之后的版本,新增加了一种字符集:utf8mb4,最多可以支持 4 个字节的 UTF-8 字符。

如果你的数据库是 5.5.3 及之后的版本,那么可以按照如下步骤进行操作,让数据库来支持 4 字节的 UTF-8 字符(其实主要是为了能够存储 Emoji 字符):

断开你的应用到数据库的连接

如果你的应用还连接着数据库,那么把应用停下来吧,只留下你要进行操作的终端连接到数据库。

完整备份数据库

这一步非常重要,不要怕麻烦,完整备份你的数据库,以防万一。

更改数据库的字符集

首先,修改数据库的字符集。如果你的数据库中文本查找是对大小写敏感的(这种情况应该很少的),请使用 utf8mb4_bin 来替代下面 SQL 中的 utf8mb4_unicode_ci。

更新库表字符集

然后,对每个库表进行字符集变更,以便后续向表中增加数据列的时候不用再显式的指明新增加的列的字符集:

更新库表列的字符集

可以对表中的每个列进行字符集的控制:

更新数据库索引

如果你将被索引的列的字符集修改为 utf8mb4,那么你可能会遇到这样的错误。

在 InnoDB 引擎:

在 MyISAM 引擎:

如果你使用的是 MySQL 5.6.3 及以上的版本 + InnoDB 引擎,很简单,只需修改 MySQL 的配置文件中的 innodb_large_prefix 这个选项。关于这个选项的详细描述请参考 MySQL 官方文档:sysvar_innodb_large_prefix

如果很不幸,你的数据库还不到 5.6.3 版本,那么就比较麻烦了。你需要把出现问题的索引(通常是包含了 VARCHAR 类型的列)删除掉,然后重新创建,并且限制要索引的数据长度。

那么,问题来了,为什么是 191 这个奇怪的数字呢?以 InnoDB 为例,如果不打开 innodb_large_prefix 这个选项的话(当然了,如果能打开这个选项,就不用在这里啰嗦了),那么一个索引最多容纳 767 个字节。我们知道,对于 MySQL 中的 VARCHAR(n) 类型的数据,这里的 n 实际上指的是字符的数量。如果使用 utf8mb4 编码存储字符的话,每个字符最多占用 4 个字节,那么 n 个字符所占用的就是 4 * n 个字节。所以,我们索引前 191 个字符,那么最多占用 4 * 191 = 764 字节,就不会超过 767 的限制了。

最后,别忘了修改客户端连接的字符集

无论是使用 Java 还是 PHP,都需要在连接中指明使用 utf8mb4 字符集,否则还是会出现问题。

如果你用命令行,那么需要在 MySQL 配置文件中修改客户端的字符集:

如何查看当前客户端、连接、数据库等和字符集相关的信息?

会得到这样的结果:

Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/

你实际运行出来的结果可能和上面表格中的不完全一致,这个不是问题。

连接数据库之后再修改连接所用的字符集:


分割线


如果不需要修改整个库的字符集编码,通常情况下也确实不需要整个库的修改,那么可以针对单独的列来修改,修改方式请参考上面的内容。但是还是别忘了将客户端连接中的字符集修改为 utf8mb4,不要怕,这个字符集是能够兼容 utf8 的,也就是说,对于那些未修改为 utf8mb4 字符集的表和列,都不会有问题。

One Comment

  • www.ca788.com 回复

    自己打败自己是最可悲的失败,自己战胜自己是最可贵的胜利。

发表评论

电子邮件地址不会被公开。 必填项已用*标注