mysql如何存储emoj表情

前言：

       DBA经常性会遇到RD小伙伴询问，为什么我的数据库无法存储emoj的表情？为什么我的数据库都设置成了utf8mb4插入emoj表情还是报错？为什么我的emoj表情插入到数据库里面了，可是显示的是问号？

如：

Caused by: java.sql.SQLException: Incorrect string value: '\xF6\x9D\x98\x84' for column 'comment' at row 1

这里先直接给出结论：

       只要保证存储emoj表情包的表的字段为utf8mb4的字符集即可。即：column column_type CHARACTER SET utf8mb4。

验证：

1.创建表：

CREATE TABLE `test_emotion` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `emotion` varchar(200) CHARACTER SET utf8mb4 DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

2.mysql环境变量

 

3.使用flask简单的搭建一个web页面

@app.route('/save/',methods=['POST', 'GET']) def save():     if request.method == 'POST':         try:             emotion = request.form['emotion']             conn = pymysql.connect(host='xxx',                                    user='xxx',                                    passwd='xxx',                                    port=3306,                                    connect_timeout=5,                                    charset='utf8mb4',                                    db='wang_test',                                    cursorclass=pymysql.cursors.DictCursor)             cursor = conn.cursor()             query = "insert into test_emotion (emotion) values (%s);"             cursor.execute(query, [emotion])             cursor.close()             conn.commit()             return redirect(url_for('emotion_index'))         except Exception as error:             return render_template('save.html', error=error)     else:         return render_template('save.html')   @app.route('/emotion/') def emotion_index():     conn = pymysql.connect(host='xxx',                            user='xxx',                            passwd='xxx',                            port=3306,                            connect_timeout=5,                            charset='utf8mb4',                            db='wang_test',                            cursorclass=pymysql.cursors.DictCursor)     cursor = conn.cursor()     query = "select emotion from test_emotion;"     cursor.execute(query)     emotion_dict = cursor.fetchall()     print(emotion_dict)     cursor.close()     conn.commit()     return render_template('emotion.html', emotion_dict = emotion_dict)

4.插入数据，并展示

 

 

问题解决：

1）确保存储emoj表情相应表的字段为utf8mb4。

2）确保同mysql建立的连接使用utf8mb4。

• 如上例pymysql.connect中的字段charset='utf8mb4'。
• 类似jdbc配置文件：String url="jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=utf8"。其中characterEncoding=utf8强行指定字符集针对不同的驱动版本后端识别的字符集映射也不同。

区别	Connector/J 5	Connector/J 8
驱动	com.mysql.jdbc.Driver（Connector/J <=5）	com.mysql.cj.jdbc.Driver（Connector/J 8）
useUnicode	useUnicode=true	废弃
characterEncoding=UTF-8	utf8（utf8mb3）（Connector/J <=8.0.12）	utf8mb4（Connector/J >=8.0.13）
useSSL	useSSL=true/false	废弃
sslMode	无	sslMode=PREFERRED
serverTimezone	无	需指定，serverTimezone=GMT%2B8

可以看到在8.0.13驱动版本前utf8映射的是mysql的utf8字符集，因此识别不了emoj表情。此处需要将characterEncoding=utf8去掉选择驱动智能匹配。不可以将连接串的utf8改为utf8mb4，会报错：Caused by: java.io.UnsupportedEncodingException: utf8mb4。

3）确保mysql驱动版本大于5.1.13，建议使用8.0.13+(这样就不需要去掉characterEncoding=utf8了会自动映射成utf8mb4)。下载地址 https://downloads.mysql.com/archives/c-j/ 。老版本确实需要修改mysql的character_set_server=utf8mb4并重启实例。

4）确保mysql环境变量init_connect为空或者为SET NAMES utf8mb4;(通常不需要设置)

      驱动版本>=8.0.13的时候会通过set names utf8mb4来初始化连接，因此可能会造成冲突。

5）不要再修改mysql的配置文件环境和variables并重启实例了(ps：生产环境哪儿能随意重启，别再被网上的转发文档误导了)。