解决 Mysql 存取 emoji 表情的问题

当所要存取的字符串中包含 emoji 表情。如果不进行设置，会出现 DatabaseError

>>> sql = '''INSERT INTO review(question_id, quote, review_have_picture, is_adopt, content)VALUES("7332309", "非引用", "无图", "未接受", "修车的肯定知道😀");'''
>>> cursor.execute(sql)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/cursor.py", line 515, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/connection.py", line 488, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'content' at row 1

解决方案

把问题消灭在摇篮里：

1. 定义 database 时就指定 默认编码为 utf8mb4

CREATE DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

2. 然后定义 table 时，也更改默认编码:

CREATE TABLE table_name (test CHAR(255)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 修改 /etc/my.cnf(Linux) 文件:
   mac 下不存在该文件，需自行在 /etc 下新建文件，注意 sudo 和 chmod。

[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

4. 重启 mysql:
   mac:

mysql.server restart

centos7:

systemctl restart mysqld

此时进入后台应该就能正常存了:

Paste_Image.png

注意其他端传入到数据库是必须要保证 utf8mb4。

示例:

import mysql.connector
# 这里必须要指定 charset='utf8mb4'
conn = mysql.connector.connect(user='root', password='password', database='db_name', charset='utf8mb4')