插入表情进mysql报错_python MySQL_client 存储表情到 MySQL 报错

问题如下

使用 navicate 管理工具, 可以正常执行SQL

使用 python 代码插入数据到mysql报错, 数据库版本 8.0

报错信息如下

MySQLdb._exceptions.OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x90\\xAF' for column 'digest' at row 1")

执行的SQL

insert into wechat_article values (null, '虎牙在全球 DNS 秒级生效上的实践', '🐯', 'http://mp.weixin.qq.com/s?__biz=MzIxMjE5MTE1Nw==&mid=2653198391&idx=2&sn=32020f7f751d819154938407268746e3&chksm=8c99eaedbbee63fba144b7877c318764aaa8584e62ec174133d1d9ccae4a11ba3ce4b5235612&scene=27#wechat_redirect', 'http://mmbiz.qpic.cn/mmbiz_jpg/qdzZBE73hWv3P1NgUjEtnhvPVuMCjibe8nlSvYMWf0Wt6ibSibqsYcYHgZFMQGmaQNII3cRWad9LmgqqkOslzgIibg/0?wx_fmt=jpeg', '周健&李志鹏')

数据库和数据表

CREATE DATABASE `wechat_article` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */

CREATE TABLE `wechat_article` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,

`digest` text COLLATE utf8mb4_bin,

`content_url` text COLLATE utf8mb4_bin,

`cover` text COLLATE utf8mb4_bin,

`author` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `title` (`title`),

KEY `author` (`author`)

) ENGINE=InnoDB AUTO_INCREMENT=1361 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

python 代码

import MySQLdb

con = MySQLdb.connect(

host='127.0.0.1',

port=3306,

user='wechat_article',

passwd='wechat_article',

db='wechat_article',

charset='utf8mb4'

)

cursor = con.cursor()

# sql = "insert into wechat_article values (%s,%s,%s,%s,%s,%s)"

sql = "insert into wechat_article values(Null, '虎牙在全球 DNS 秒级生效上的实践', '🐯', 'http://mp.weixin.qq.com/s?__biz=MzIxMjE5MTE1Nw==&mid=2653198391&idx=2&sn=32020f7f751d819154938407268746e3&chksm=8c99eaedbbee63fba144b7877c318764aaa8584e62ec174133d1d9ccae4a11ba3ce4b5235612&scene=27#wechat_redirect', 'http://mmbiz.qpic.cn/mmbiz_jpg/qdzZBE73hWv3P1NgUjEtnhvPVuMCjibe8nlSvYMWf0Wt6ibSibqsYcYHgZFMQGmaQNII3cRWad9LmgqqkOslzgIibg/0?wx_fmt=jpeg', '周健&李志鹏');"

cursor.execute(sql)

con.commit()

cursor.close()

con.close()