MySQL Python使用ON DUPLICATE KEY UPDATE更新JSON字段报错如何解决

错误原因分析 #

• 第一个1064语法错误：mysql-connector-python的executemany方法在处理ON DUPLICATE KEY UPDATE子句时，默认无法直接复用INSERT部分定义的同名命名参数，导致SQL解析失败。
• 第二个JSON格式错误：手动给%(bar)s加单引号的写法会触发双重转义：参数绑定机制本身会自动给字符串类型的参数添加必要的引号和转义处理，额外添加的单引号会导致最终存入的JSON字符串外层多包裹了一层引号，无法被MySQL的JSON字段正常解析。

最优解决方案 #

直接使用MySQL原生的VALUES()函数引用本次插入尝试对应字段的值，无需重复传递参数，修改后的SQL语句如下：

sql = '''
    INSERT INTO test
        (foo, bar)
    VALUES
        (%(foo)s, %(bar)s)
    ON DUPLICATE KEY UPDATE 
        bar = VALUES(bar)
'''

无需修改其他代码逻辑，执行即可正常完成插入/更新操作。

兼容高版本MySQL的备选写法 #

如果使用的是MySQL 8.0.19及以上版本，也可以使用行别名的写法，逻辑更清晰：

sql = '''
    INSERT INTO test
        (foo, bar)
    VALUES
        (%(foo)s, %(bar)s)
        AS new_row
    ON DUPLICATE KEY UPDATE 
        bar = new_row.bar
'''

注意事项 #

不需要对json.dumps的输出做额外的引号包裹处理，mysql-connector-python会自动适配JSON字段的参数绑定逻辑，只要传入合法的JSON字符串即可。

内容的提问来源于stack exchange，提问作者Spuu