最新消息:20181230 VPS服务器已从Linode换到腾讯云香港,主题仍用朋友推荐的大前端D8

【已解决】mysql插入出错:Error 1064 You have an error in your SQL syntax

MySQL crifan 62浏览 0评论

用代码:

<code>insertMediaSql = "INSERT INTO qa(id,question,answer,createTime,modifyTime,source) VALUES(%d,'%s','%s','%s','%s',%d)"
</code>

出错:

<code>Error (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'm free!','I'm free!','2018-05-30 11:22:20.487499','2018-05-30 11:22:20.487499',0' at line 1") for execute sql: INSERT INTO qa(id,question,answer,createTime,modifyTime,source) VALUES(16,'I'm free!','I'm free!','2018-05-30 11:22:20.487499','2018-05-30 11:22:20.487499',0)
</code>

很明显是:

sql语句中,值用撇号’包含着,但是内容本身:

I’m free!

却也包含着撇号

导致出错。

先去搞清楚英文的撇号的说法

英文 撇号

3种方法来用对英文里的撇号

撇号 – 维基百科,自由的百科全书

撇号( ‘ ,英语:apostrophe,又称上标点、略缩号、省字号、省年号、高撇号或缩写号)

所以再去搜:

mysql string contain apostrophe

escaping – How to escape apostrophe (‘) in MySql? – Stack Overflow

可以用两个撇号

mysql – SQL query fails with DB fields strings containing apostrophe – Stack Overflow

可以写成 ‘’

但是此处为了含义更明确,则去改为:

<code>#insertMediaSql = "INSERT INTO qa(id,question,answer,createTime,modifyTime,source) VALUES(%d,'%s','%s','%s','%s',%d)"
insertMediaSql = """
  INSERT INTO qa(`id`,`question`,`answer`,`createTime`,`modifyTime`,`source`)
  VALUES(%d,"%s","%s","%s","%s",%d)"""
</code>

且处理之前,要把双引号本身过滤掉:

<code>punctuation = [",", ".", "?", "!", "-", ";", '"']
</code>

试试结果:

就消除此问题了。

【总结】

注意在写sql时,要确保,如果外部用单引号’,比如:

<code>'x y'z'
</code>

时其中内部的单引号(撇号),要写成:

<code>'x y''z'
</code>

或者是换成双引号在外部:

<code>“x y'z"
</code>

才可以。

【后记】

在前面改为,最外部用双引号把字符串括起来,结果又遇到:

<code>Error (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'Let It Be?"","2018-05-31 12:04:52.240980","2018-05-31 12:04:52.240980",0)\' at line 2') for execute sql: 
                      INSERT INTO qa(`id`,`question`,`answer`,`createTime`,`modifyTime`,`source`)
                      VALUES(54,"What a great song.","How about "Let It Be?"","2018-05-31 12:04:52.240980","2018-05-31 12:04:52.240980",0)
</code>

所以:

看起来这个方式不行。

可以考虑用:

单引号,要把值放到sql之前,可以把1个单引号替换为2个单引号

或者再去找找别的办法

mysql contain quote string

string – How to escape single quotes in MySQL – Stack Overflow

When to use Single Quote, Double Quote, and Backticks when writing queries in MySQL

感觉是:

为了支持单引号,和双引号,则:

外部用单引号

内部有单引号的,写成:\’

内部有双引号的,直接用”

去改为:

<code>dialogA = dialogAB[0]
dialogB = dialogAB[1]
dialogA = dialogA.replace("'", "''")
dialogB = dialogB.replace("'", "''")
if len(dialogA) &gt; 0 and len(dialogB) &gt; 0:
    #insertMediaSql = "INSERT INTO qa(id,question,answer,createTime,modifyTime,source) VALUES(%d,'%s','%s','%s','%s',%d)"
    insertMediaSql = """
      INSERT INTO qa(`id`,`question`,`answer`,`createTime`,`modifyTime`,`source`)
      VALUES(%d,'%s','%s','%s','%s',%d)"""
    qaid = curId
    executeSql = insertMediaSql % (qaid, dialogA, dialogB, now, now, 0)
</code>

结果就可以了。

转载请注明:在路上 » 【已解决】mysql插入出错:Error 1064 You have an error in your SQL syntax

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
57 queries in 0.080 seconds, using 9.96MB memory