mysql - Mysql 1366 错误 - 使用了 utf8mb4 编码
问题描述
def connect():
conn = mysql.connector.connect(host='localhost', database='otrs', user='root', password='password', autocommit=True)
if conn.is_connected():
print('connected')
sqlstr = "SELECT ticket.id, article.id, ticket_history.create_time, article.a_body FROM ticket, ticket_history, article WHERE ticket_history.ticket_id=ticket.id AND ticket_history.article_id=article.id AND (ticket.ticket_state_id=2 OR ticket.ticket_state_id=3) AND ticket_history.name ='%%Close' ;"
cursor.execute(sqlstr)
for row in cursor.fetchall():
print row
val= row[3].replace('\'','')
print val
sqlstr1 = "INSERT INTO temp VALUES (%s, %s, '%s','%s')" %(row[0], row[1], row[2], val)
cursor.execute(sqlstr1)
print 'done'
我编写了一个 python 查询来从 mysql 表中的表中插入选择数据并将它们写入临时表。当我执行查询时,插入几行数据后,它会引发一个异常,例如
DatabaseError: 1366 (HY000): Incorrect string value: '\xE2\x80\x8BWil...'
DatabaseError: 1366 (HY000): Incorrect string value: '\xE2\x80\x8BVid...'
DatabaseError: 1366 (HY000): Incorrect string value: '\xE2\x80\x8BSol...'
提出问题的条目是,
(2932, 10503, datetime.datetime(2016, 10, 19, 17, 2, 7), u'Hi Arshadh,\n\nThis has been configured on PR FWSM device only\n\nBR,\n\u200bViduna\n\xa0')
(3136, 13353, datetime.datetime(2016, 11, 25, 12, 40, 35), u'This has been postponed as we need support from forinet TAC team to resolve\nthis.\nWaiting for their feedback.\n\u200bWill raise new ticket when we get update from them\n')
(3661, 18395, datetime.datetime(2017, 1, 27, 15, 34, 45), u'This request has been performed on 1/26/2017,\n\u200bSince the testing is getting delayed- closing the cr\xa0\nwe can reopen this again if there is any problem.\n')
但以下数据集不会引发错误,
(3672, 18393, datetime.datetime(2017, 1, 27, 15, 28, 9), u'This request has been performed on 1/26/2017,\nSince the testing is getting delayed- closing the cr\xa0\nwe can reopen this again if there is any problem.\n')
因此,如果有 \n\u200b 而不是 \n ,则会引发问题。我到处搜索,但找不到解决方案。我认为错误是因为 ASCII 特殊字符。但我不知道如何解决这个问题。
解决方案
访问https://mathiasbynens.be/notes/mysql-utf8mb4 这是因为 MySQL 的 utf8 字符集仅部分实现了正确的 UTF-8 编码。所以将数据库转换为 utf8mb4 解决了这个问题。
对于每个数据库:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
对于每个表:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
执行这些查询,解决了这个问题。
推荐阅读
- python-3.x - 仅将最后一个 shell 命令的标准输出放在 Python 变量中
- csv - 是否可以使基数 60 浮动
- python - 我在 Python 中面临一个问题,用户说“是”或“否”,无论如何循环仍然执行。为什么会这样?
- angular - 从沙盒解决方案到 SharePoint 2013 站点的 REST 调用与 JSOM 调用
- python-3.x - 如何重写这个递归函数来生成一个有yield的生成器?
- c++ - 错误 C2971 具有非静态存储持续时间的变量不能用作非类型参数
- python - 在进行逻辑回归时,是否有执行逐步向前或向后选择的函数?
- python - 变量作为功能绑定
- javascript - window.opener.location.href 历史在 Firefox 上不起作用
- c# - 将 Selected Combobox 项目转换为小数并将其存储在数据库中