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.插入数据,并展示

image.png

image.png
问题解决:
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 5Connector/J 8
驱动com.mysql.jdbc.Driver(Connector/J <=5)com.mysql.cj.jdbc.Driver(Connector/J 8)
useUnicodeuseUnicode=true废弃
characterEncoding=UTF-8utf8utf8mb3)(Connector/J <=8.0.12)utf8mb4(Connector/J >=8.0.13)
useSSLuseSSL=true/false废弃
sslModesslMode=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:生产环境哪儿能随意重启,别再被网上的转发文档误导了)。

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://www.fengpt.cn/archives/mysql如何存储表情