解决Python插入数据到MySQL时遇到的Incorrect string value错误
问题与原因
使用python执行插入语句将数据插入到MySQL时抛出了以下异常
pymysql.err.InternalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x91\\x8D, ...' for column 'content' at row 1")
以上错误是由编码问题造成的,你使用的数据库默认编码是utf8,可以保存1到3个字节,但是你插入到数据库中的字符串包含emoji表情字符(占用4个字节),因此会抛出Incorrect string value异常。
解决方法
解决的方法主要有以下两种
- 修改MySQL的编码格式
- 在程序中过滤emoji表情字符
修改MySQL的编码格式
MySQL从5.5.3版本开始,才支持4个字节的utf8编码,编码名称是utf8mb4(mb4意思为max bytes 4),在MySQL中执行以下SQL语句可以看到utf8和utf8mb4的相关信息
SELECT * FROM information_schema.CHARACTER_SETS
WHERE CHARACTER_SET_NAME LIKE 'utf8%'
结果如下
CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
---|---|---|---|
utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |
因此,将MySQL编码改为utf8mb4就可以解决这个问题。
解决程序的编码问题需要进行以下几个操作:
-
修改my.cnf配置
找到MySQL的配置文件my.cnf(windows系统一般在MySQL的安装目录中,linux系统放在/etc目录下)
修改含有utf8编码的参数为utf8mb4,如下
character-set-server=utf8mb4 [client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4
修改保存后,重启MySQL。
-
修改数据库和数据表的编码格式
修改数据库编码(更改
db_name
为你的数据库)ALTER DATABASE `db_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
修改数据表编码(更改
table_name
为你的数据表)。ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-
修改python连接pymysql的配置信息
去掉
charset
参数或将charset
参数设置为utf8mb4,如:MYSQL_CONFIG = { 'host': 'localhost', # IP地址 'port': 3306, # 端口 'user': 'root', # 用户名 'passwd': '123456', # 密码 'db': 'mydb', # 数据库 # 'charset': 'utf8mb4', # 字符编码 }
再次执行你的程序就可以正常插入数据了。
在程序中过滤emoji表情字符
为了避免出现以上错误,我们还可以在插入数据前对数据进行清洗,过滤掉文本中的emoji表情字符,再将数据插入到表中。
要去掉文本中的emoji表情字符可以使用以下两种方法
自定义清除方法
def filter_emoji(desstr,restr=''):
#过滤表情
try:
co = re.compile(u'[\U00010000-\U0010ffff]')
except re.error:
co = re.compile(u'[\uD800-\uDBFF][\uDC00-\uDFFF]')
return co.sub(restr, desstr)
插入数据前先执行以上方法过滤emoji表情,如
content = '👍, very good!'
print(filter_emoji(content))
执行结果如下:
, very good!
使用emoji第三方包
安装
pip3 install emoji
或
python3 -m pip install emoji
官方应用示例如下:
>> import emoji
>> print(emoji.emojize('Python is :thumbs_up:')) # 编码
Python is 👍
>> print(emoji.emojize('Python is :thumbsup:', use_aliases=True))
Python is 👍
>> print(emoji.demojize('Python is 👍')) # 解码
Python is :thumbs_up:
>>> print(emoji.emojize("Python is fun :red_heart:"))
Python is fun ❤
>>> print(emoji.emojize("Python is fun :red_heart:",variant="emoji_type"))
Python is fun ❤️ #red heart, not black heart
从上面例子可知,我们可以使用demojize()方法来处理emoji表情,demojize()方法的作用是将特殊字符转换为正常字符(相当于解码)。
import emoji
content = '👍, very good!'
print(emoji.demojize(content))
执行结果如下
:thumbs_up:, very good!
因此,我们就可以将解码后的文本插入到MySQL数据库中。
总结
以上就是Incorrect string value异常产生的原因和解决方法。如果对文本要求不大,本人比较推荐第二种方法,过滤emoji表情字符后再插入到数据库中。否则,就需要修改相关的数据库配置来解决这个问题。