MySQL如何存储表情

前言：
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强行指定字符集针对不同的驱动版本后端识别的字符集映射也不同。

      可以看到在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：生产环境哪儿能随意重启，别再被网上的转发文档误导了)。