csdnceshi63
elliott.david
采纳率25%
2009-02-14 05:24

"插入忽略"vs"插入... 在重复键更新"

已采纳

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either:

  • ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or
  • INSERT IGNORE which implies an invitation for other kinds of failure to slip in unannounced.

Am I right in these assumptions? What's the best way to simply skip the rows that might cause duplicates and just continue on to the other rows?

转载于:https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update

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

9条回答

  • weixin_41568134 MAO-EYE 12年前

    I would recommend using INSERT...ON DUPLICATE KEY UPDATE.

    If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

    • Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
    • Inserting a NULL into a column with a NOT NULL constraint.
    • Inserting a row to a partitioned table, but the values you insert don't map to a partition.

    If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

    • A new auto-increment ID is allocated.
    • Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
    • Triggers that fire on DELETE are executed unnecessarily.
    • Side effects are propagated to replication slaves too.

    correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.


    A user tried to edit this post (the edit was rejected by moderators). The edit tried to add a claim that INSERT...ON DUPLICATE KEY UPDATE causes a new auto-increment id to be allocated. It's true that the new id is generated, but it is not used in the changed row.

    See demonstration below, tested with Percona Server 5.5.28. The configuration variable innodb_autoinc_lock_mode=1 (the default):

    mysql> create table foo (id serial primary key, u int, unique key (u));
    mysql> insert into foo (u) values (10);
    mysql> select * from foo;
    +----+------+
    | id | u    |
    +----+------+
    |  1 |   10 |
    +----+------+
    
    mysql> show create table foo\G
    CREATE TABLE `foo` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `u` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `u` (`u`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
    
    mysql> insert into foo (u) values (10) on duplicate key update u = 20;
    mysql> select * from foo;
    +----+------+
    | id | u    |
    +----+------+
    |  1 |   20 |
    +----+------+
    
    mysql> show create table foo\G
    CREATE TABLE `foo` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `u` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `u` (`u`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
    

    The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u. Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row.

    Whereas REPLACE does delete the original row and inserts a new row, generating and storing a new auto-increment id:

    mysql> select * from foo;
    +----+------+
    | id | u    |
    +----+------+
    |  1 |   20 |
    +----+------+
    mysql> replace into foo (u) values (20);
    mysql> select * from foo;
    +----+------+
    | id | u    |
    +----+------+
    |  3 |   20 |
    +----+------+
    
    点赞 22 评论 复制链接分享
  • weixin_41568134 MAO-EYE 10年前

    In case you want to see what this all means, here is a blow-by-blow of everything:

    CREATE TABLE `users_partners` (
      `uid` int(11) NOT NULL DEFAULT '0',
      `pid` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`uid`,`pid`),
      KEY `partner_user` (`pid`,`uid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    

    Primary key is based on both columns of this quick reference table. A Primary key requires unique values.

    Let's begin:

    INSERT INTO users_partners (uid,pid) VALUES (1,1);
    ...1 row(s) affected
    
    INSERT INTO users_partners (uid,pid) VALUES (1,1);
    ...Error Code : 1062
    ...Duplicate entry '1-1' for key 'PRIMARY'
    
    INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
    ...0 row(s) affected
    
    INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
    ...0 row(s) affected
    

    note, the above saved too much extra work by setting the column equal to itself, no update actually needed

    REPLACE INTO users_partners (uid,pid) VALUES (1,1)
    ...2 row(s) affected
    

    and now some multiple row tests:

    INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
    ...Error Code : 1062
    ...Duplicate entry '1-1' for key 'PRIMARY'
    
    INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
    ...3 row(s) affected
    

    no other messages were generated in console, and it now has those 4 values in the table data. I deleted everything except (1,1) so I could test from the same playing field

    INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
    ...3 row(s) affected
    
    REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
    ...5 row(s) affected
    

    So there you have it. Since this was all performed on a fresh table with nearly no data and not in production, the times for execution were microscopic and irrelevant. Anyone with real-world data would be more than welcome to contribute it.

    点赞 20 评论 复制链接分享
  • csdnceshi50 三生石@ 4年前

    If using insert ignore having a SHOW WARNINGS; statement at the end of your query set will show a table with all the warnings, including which IDs were the duplicates.

    点赞 10 评论 复制链接分享
  • weixin_41568183 零零乙 4年前

    Potential danger of INSERT IGNORE. If you are trying to insert VARCHAR value longer then column was defined with - the value will be truncated and inserted EVEN IF strict mode is enabled.

    点赞 9 评论 复制链接分享
  • csdnceshi77 狐狸.fox 4年前

    If you want to insert in the table and on the conflict of the primary key or unique index it will update the conflicting row instead of inserting that row.

    Syntax:

    insert into table1 set column1 = a, column2 = b on duplicate update column2 = c;
    

    Now here, this insert statement may look different what you have seen earlier. This insert statement trying to insert a row in table1 with the value of a and b into column column1 and column2 respectively.

    Let's understand this statement in depth:

    For example: here column1 is defined as the primary key in table1.

    Now if in table1 there is no row having the value “a” in column1. So this statement will insert a row in the table1.

    Now if in table1 there is a row having the value “a” in column2. So this statement will update the row’s column2 value with “c” where the column1 value is “a”.

    So if you want to insert a new row otherwise update that row on the conflict of the primary key or unique index.
    Read more on this link

    点赞 4 评论 复制链接分享
  • csdnceshi65 larry*wei 11年前

    I know this is old, but I'll add this note in case anyone else (like me) arrives at this page while trying to find information on INSERT..IGNORE.

    As mentioned above, if you use INSERT..IGNORE, errors that occur while executing the INSERT statement are treated as warnings instead.

    One thing which is not explicitly mentioned is that INSERT..IGNORE will cause invalid values will be adjusted to the closest values when inserted (whereas invalid values would cause the query to abort if the IGNORE keyword was not used).

    点赞 3 评论 复制链接分享
  • csdnceshi58 Didn"t forge 10年前

    Something important to add: When using INSERT IGNORE and you do have key violations, MySQL does NOT raise a warning!

    If you try for instance to insert 100 records at a time, with one faulty one, you would get in interactive mode:

    Query OK, 99 rows affected (0.04 sec)

    Records: 100 Duplicates: 1 Warnings: 0

    As you see: No Warnings! This behavior is even wrongly described in the official Mysql Documentation.

    If your script needs to be informed, if some records have not been added (due to key violations) you have to call mysql_info() and parse it for the "Duplicates" value.

    点赞 2 评论 复制链接分享
  • csdnceshi64 游.程 12年前

    ON DUPLICATE KEY UPDATE is not really in the standard. It's about as standard as REPLACE is. See SQL MERGE.

    Essentially both commands are alternative-syntax versions of standard commands.

    点赞 2 评论 复制链接分享
  • csdnceshi53 Lotus@ 12年前

    Replace Into seems like an option. Or you can check with

    IF NOT EXISTS(QUERY) Then INSERT
    

    This will insert or delete then insert. I tend to go for a IF NOT EXISTS check first.

    点赞 1 评论 复制链接分享

相关推荐