MySQL 保存 emoji 表情失败报错

更新日期: 2018-01-02 阅读次数: 7098 分类: MySQL

有朋友在博客评论里反馈,无法提交带 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 聊聊。 白天写程序,晚上哄熊孩子,可能回复有点慢,见谅。 查看更多联系方式

相关文章

爱评论不评论

近期节日

2020年08月15日 日本投降日
2020年08月22日 处暑
2020年08月25日 七夕
2020年09月02日 中元节
2020年09月03日 抗日胜利纪念日
2020年09月07日 白露
2020年09月08日 国际扫盲日
2020年09月10日 教师节
2020年09月16日 国际臭氧层保护日
2020年09月16日 世界清洁地球日
2020年09月18日 "九一八"事变纪念日
2020年09月20日 国际爱牙日
查看更多节日