doulu1544
doulu1544
2013-09-20 13:31

SQL拒绝批量插入,因为单行是重复的

已采纳

I don't know the proper term, but by "Batch insert" what I mean is..

INSERT INTO `table` (`column1`, `column2`, `column3`) VALUES 
("value1", "value2", "value3"),
("value4", "value5", "value6"),
("value7", "value8", "value9")

We're inserting multiple rows in a single query to save resources.

We have a composite key set up on this table to avoid getting duplicate records. Problem is, when there is a duplicate record, it rejects the entire "batch", so in the example above, if "value1" already existed in the column1 column, it will not insert any of the 3 rows.

We are using PHP with the mysql_ functions (yes, I know they're being deprecated, let's address one problem at a time please)

So my question is, is there a way to insert multiple rows at once where, in the event of a duplicate row, it will still insert the rest of the rows in the "batch"?

Thanks for your help.

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

2条回答

  • douhanzhen8927 douhanzhen8927 8年前

    Try the ignore keyword

    INSERT IGNORE INTO `table` ...
    

    If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

    点赞 评论 复制链接分享
  • dshgdhdfcas30210 dshgdhdfcas30210 8年前

    complementing juerguen's answer, you could also use:

    INSERT INTO thetable (pageid, name)
    VALUES (1, "foo"), (1, "foo")
    ON DUPLICATE KEY UPDATE (pagecount = pagecount + 1)
    

    that way, you could identify the duplicated row and delete it when the process is finished.

    For example, adding a flag field to the table named is_duplicated

    INSERT INTO thetable (pageid, name)
    VALUES (1, "foo"), (1, "foo")
    ON DUPLICATE KEY UPDATE (is_duplicated = 1)
    

    And later:

    DELETE FROM thetable WHERE is_duplicated = 1
    

    Also you could use:

    SET foreign_key_checks = 0;
    

    execute the batch, and then:

    SET foreign_key_checks = 1;
    

    That way, if there is a foreign key that may not exist at that time but it will be created later, the inserts will also keep going.

    点赞 评论 复制链接分享

相关推荐