MySQL 保存 emoji 表情失败报错

有朋友在博客评论里反馈，无法提交带 emoji 表情的评论。第一反应是数据库编码问题。

由于一直不习惯使用 emoji，所以这个问题一直没发现。

Laravel 的报错信息

production.ERROR: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation ‘=’

MySQL CHARSET 与 COLLATE 的区别

https://stackoverflow.com/questions/341273/what-does-character-set-and-collation-mean-exactly

CHARSET 即字符集编码。例如，一个字符集只有两个字符，假设 A 用 1 来编码，B 用 2 来编码。
COLLATE 即对比方法。用于指定数据集如何排序，以及字符串的比对规则。例如，ci，即大小写不敏感。

所以，当你看 wordpress 的建表语句时，会发现既指定了 CHARSET，也指定了 COLLATE。

而对于具体字段又单独指定了 COLLATE，我猜测是为了保证排序及比对的规则。

CREATE TABLE `wp_posts` (
  `post_title` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=324 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

如何查看当前数据库 CHARSET 及 COLLATE

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA 
    -> WHERE schema_name = "db_2018";
+----------------------------+
| default_character_set_name |
+----------------------------+
| latin1                     |
+----------------------------+
1 row in set (0.00 sec)

没想到我居然用的是 latin1 …

查看当前数据表 CHARSET 及 COLLATE

mysql> SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
    ->        information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
    -> WHERE CCSA.collation_name = T.table_collation
    ->   AND T.table_schema = "db_2018"
    ->   AND T.table_name = "products";
+--------------------+
| character_set_name |
+--------------------+
| utf8               |
+--------------------+
1 row in set (0.01 sec)

果然，数据表使用了 utf8 charset，而不是 utf8mb4。

但是这样查看，非常的不直观，因为我还想同时看到每列的设置，所以，远不如

mysql> show create table products;

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `desc` text COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=615 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

如何修改数据表及字段的编码

修改数据表的编码之后，会默认将字段一并修改。执行结果如下

mysql> ALTER TABLE products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> show create table products;

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `desc` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

如何修改数据库的编码

虽然改了数据表就不需要再改数据库的编码了，但是还是统一一下比较好。

ALTER DATABASE db_2018 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Laravel 的默认编码

打开 laravel 5.5 config/database.php 文件，会发现默认的 mysql 编码就是 utf8mb4。

Laravel 好样的。。。

关于作者 🌱

我是来自山东烟台的一名开发者，有感兴趣的话题，或者软件开发需求，欢迎加微信 zhongwei 聊聊，或者关注我的个人公众号“大象工具”，查看更多联系方式