dqgo99177
dqgo99177
2014-08-25 13:25

ON DUPLICATE KEY UPDATE上的SQL语法错误

已采纳

I have this table that I want to update / insert with one statement currently using ON DUPLICATE KEY UPDATE:

index (INT) user(INT) entryId(INT) tags(text)
1              1          111      ||bla||
2              1          111      ||bla||
3              1          111      ||bla||

INSERT INTO filters (index,user,entryId,tags) VALUES (1,1,'100003817186741',"||test1||||test2||"), (3,1,'100003021196089',"||test1||||test2||") ON DUPLICATE KEY UPDATE user=VALUES(user),entryId=VALUES(entryId),tags=VALUES(tags)

Why does this fail with an SQL syntax error?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • dongzhang5006 dongzhang5006 7年前

    It fails because index is a MySQL reserved word.

    http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

    either wrap it in backticks or choose another word for it

    INSERT INTO filters (index,user,entryId,tags)
                         ^    ^
    

    do

    INSERT INTO filters (`index`,user,entryId,tags)
    
    点赞 评论 复制链接分享
  • duanjiancong4860 duanjiancong4860 7年前

    Your syntax looks strange with four vertical bars together and mixing single quotes and double quotes.

    INSERT INTO filters(index, user, entryId, tags)
        VALUES (1, 1, '100003817186741', "||test1||||test2|| "),  
               (3, 1, '100003021196089', "||test1||||test2|| ")
        ON DUPLICATE KEY UPDATE user=VALUES(user),entryId=VALUES(entryId),tags=VALUES(tags);
    

    I would write this more like:

    INSERT INTO filters(`index`, `user`, entryId, tags)
        SELECT 1, 1, '100003817186741', CONCAT('||', test1, '||', test2) UNION ALL
        SELECT 3, 1, '100003021196089', CONCAT('||', test1, '||', test2)
        ON DUPLICATE KEY UPDATE `user` = VALUES(`user`),
                                entryId = VALUES(entryId),
                                tags = VALUES(tags);
    

    That is, using the explicit concat() function. The use of select instead of values is just a personal preference -- select . . . union all does everything values does and more.

    Also, avoid using reserved words such as index as column or table names. These are reserved words and SQL is much more readable without escape characters.

    点赞 评论 复制链接分享
  • douren7179 douren7179 7年前

    index is a SQL reserved words, you must rewrite your query

    INSERT INTO filters (`index`,`user`,entryId,tags) VALUES (1,1,'100003817186741',"||test1||||test2||"), (3,1,'100003021196089',"||test1||||test2||") ON DUPLICATE KEY UPDATE `user`=VALUES(`user`),entryId=VALUES(entryId),tags=VALUES(tags)
    
    点赞 评论 复制链接分享

相关推荐