Python / MySQL:在简单的文本插入上“截断不正确的双值...”

时间:2021-07-18 17:08:19

I've got a problem with a presumably simple text insert into a MySQL database from Python. The text is a HTML string, with which I want to update an existing row based on this rows primary key. I'm using this statement with PyMySQL (line breaks added for readability):

我有一个问题,可能是简单的文本从Python插入MySQL数据库。该文本是一个HTML字符串,我希望根据此行主键更新现有行。我在PyMySQL中使用了这个语句(为了便于阅读,添加了换行符):

cursor.execute("UPDATE my_table SET my_text_col = %s
    AND another_int_col = %s WHERE my_table_pk_col = %s",
    (some_html_string, some_int, this_rows_pk))

This should be fairly straightforward, but (as way to often with MySQL) it apparently isn't. I get

这应该是相当简单的,但(对于经常使用MySQL的方式)它显然不是。我明白了

Warning: (1292, Truncated incorrect DOUBLE value: [Beginning of some_html_string])

and the my_text_col is set to 0.

并且my_text_col设置为0。

I have no idea why this doesn't work. There is no double field in this table, the HTML is a string that gets properly escaped, the other two values are ints. I have seen that others reported similar issues, and there are even bug reports related to this (#43437, #46641), but these are more than 8 years old and haven't been fixed (I'm using MySQL 5.6.27 and PyMySQL 0.7.11).

我不知道为什么这不起作用。此表中没有双字段,HTML是正确转义的字符串,其他两个值是整数。我已经看到其他人报告了类似的问题,甚至还有与此相关的错误报告(#43437,#46641),但这些已超过8年且尚未修复(我正在使用MySQL 5.6.27和PyMySQL 0.7.11)。

If anyone has solution or a workaround to get this simple update done I would greatly appreciate it.

如果有人有解决方案或解决方法来完成这个简单的更新,我将非常感激。

1 个解决方案

#1


1  

That's a simple but not obvious syntax error:

这是一个简单但不明显的语法错误:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

This is the syntax from the docs (https://dev.mysql.com/doc/refman/5.7/en/update.html), multiple fields are seperated with commas not with AND in update statements.

这是文档(https://dev.mysql.com/doc/refman/5.7/en/update.html)中的语法,多个字段用逗号分隔,而不是在更新语句中使用AND。

#1


1  

That's a simple but not obvious syntax error:

这是一个简单但不明显的语法错误:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

This is the syntax from the docs (https://dev.mysql.com/doc/refman/5.7/en/update.html), multiple fields are seperated with commas not with AND in update statements.

这是文档(https://dev.mysql.com/doc/refman/5.7/en/update.html)中的语法,多个字段用逗号分隔,而不是在更新语句中使用AND。