mysql 解决 emoji_解决 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 "", line 1, in

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

解决方案

把问题消灭在摇篮里：

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

CREATE DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

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

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

修改 /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

重启 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')