mysql 数据库保存不了emoji问题解决

最新推荐文章于 2021-07-15 22:54:42 发布

weixin_34199405

最新推荐文章于 2021-07-15 22:54:42 发布

阅读量195

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/miaojiangmin/blog/1830378

本文详细介绍了如何配置MySQL使用utf8mb4字符集，以支持包含Emoji表情在内的四字节Unicode字符。涵盖配置文件调整、数据库及表的字符集变更等关键步骤。

2019独角兽企业重金招聘Python工程师标准>>>

[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

init_connect='SET NAMES utf8mb4'

注意：mysql支持utf8mb4的版本是5.5.3+，必须升级到较新版本 SHOW VARIABLES WHERE Variable_name LIKE ‘version%’;

执行数据库和表、字段的字符集sql:

ALTER DATABASE t_yown_userdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE t_yown_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

alter table t_yown_user modify `NICKNAME` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '昵称';

检验是否修改好：

SHOW VARIABLES WHERE Variable_name LIKE ‘character_set_%’ OR Variable_name LIKE ‘collation%’

character_set_client    utf8mb4

character_set_connection    utf8mb4

character_set_database    utf8mb4

character_set_filesystem    binary

character_set_results    utf8mb4

character_set_server    utf8mb4

character_set_system    utf8

character_sets_dir    /usr/share/mysql/charsets/

collation_connection    utf8mb4_general_ci

collation_database    utf8mb4_unicode_ci

collation_server    utf8mb4_unicode_ci

二、确认mysql驱动、连接串：

确保mysql connection版本高于5.1.13否则仍然不能试用utf8mb4

确认连接串 jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/t_yown_userdb?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE
```[解决 Mysql 存取 emoji 表情的问题](https://www.jianshu.com/p/2154ade4abbd "解决 Mysql 存取 emoji 表情的问题")


```shell
>>> 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

定义 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

 
升级原因
MySQL默认的utf8只支持三字节字符，不支持Emoji表情符（四字节），如果有四字节的字符写入会报错。从MySQL 5.5开始，提供了utf8mb4，支持四字节的字符。
许多使用iphone的用户来在填写昵称的时候会加入Emoji表情符，如果MySQL没有使用utf8mb4字符集，这样的昵称信息就无法写入。

一、升级前。
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------------------------------------------+
| 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       | /home/sdkserver/local/mysql-5.5.43-linux2.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
二、修改配置文件。
打开MySQL的配置文件，将字符集的配置修改成如下：

[client]  
default-character-set = utf8mb4

[mysql]  
default-character-set = utf8mb4

[mysqld]  
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
三、升级后。
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------------------------------------------+
| Variable_name            | Value                                                              |
+--------------------------+--------------------------------------------------------------------+
| character_set_client     | utf8mb4                                                            |
| character_set_connection | utf8mb4                                                            |
| character_set_database   | utf8mb4                                                            |
| character_set_filesystem | binary                                                             |
| character_set_results    | utf8mb4                                                            |
| character_set_server     | utf8mb4                                                            |
| character_set_system     | utf8                                                               |
| character_sets_dir       | /home/nieyong/local/mysql-5.5.43/share/charsets/                   |
+--------------------------+--------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql设置字符集utf8mb4

MySQL 解决 emoji表情的方法，使用utf8mb4 字符集(4字节 UTF-8 Unicode 编码)

转载于:https://my.oschina.net/miaojiangmin/blog/1830378