"INSERT IGNORE"vs "INSERT… ON DUPLICATE KEY UPDATE"

"INSERT IGNORE"vs "INSERT… ON DUPLICATE KEY UPDATE"

---

INSERT행이 많은 문을 실행하는 동안 실패를 유발할 수있는 중복 항목을 건너 뛰고 싶습니다. 몇 가지 조사 후 내 옵션은 다음 중 하나를 사용하는 것으로 보입니다.

• ON DUPLICATE KEY UPDATE 일부 비용으로 불필요한 업데이트를 의미합니다.
• INSERT IGNORE 이는 예고없이 다른 종류의 실패에 대한 초대를 의미합니다.

내가 이러한 가정에 맞습니까? 중복을 유발할 수있는 행을 건너 뛰고 다른 행으로 계속 진행하는 가장 좋은 방법은 무엇입니까?

---

사용하는 것이 좋습니다 INSERT...ON DUPLICATE KEY UPDATE.

를 사용 INSERT IGNORE하면 중복 키가 발생하면 행이 실제로 삽입되지 않습니다. 그러나 문은 오류를 생성하지 않습니다. 대신 경고를 생성합니다. 이러한 경우는 다음과 같습니다.

• PRIMARY KEY또는 UNIQUE제약 조건이 있는 열에 중복 키 삽입 .
• NOT NULL제약 조건이 있는 열에 NULL을 삽입합니다 .
• 파티션을 나눈 테이블에 행을 삽입하지만 삽입하는 값은 파티션에 매핑되지 않습니다.

를 사용하는 REPLACE경우 MySQL은 실제로 a DELETE뒤에 INSERT내부적 으로를 수행하므로 예기치 않은 부작용이 있습니다.

• 새로운 자동 증가 ID가 할당됩니다.
• 외래 키가있는 종속 행을 삭제하거나 (계단식 외래 키를 사용하는 경우) REPLACE.
• DELETE실행되는 트리거 는 불필요하게 실행됩니다.
• 부작용은 복제 슬레이브에도 전파됩니다.

수정 :REPLACE 및 둘 다 INSERT...ON DUPLICATE KEY UPDATEMySQL에 특정한 비표준 독점 발명품입니다. ANSI SQL 2003은 MERGE동일한 요구 사항 (및 그 이상)을 해결할 수 있는 문을 정의 하지만 MySQL은이 MERGE문을 지원하지 않습니다 .

---

사용자가이 게시물을 편집하려고했습니다 (조정자가 편집을 거부했습니다). 수정 사항 INSERT...ON DUPLICATE KEY UPDATE이 새 자동 증가 ID가 할당되도록 하는 청구를 추가하려고했습니다 . 새 ID가 생성 된 것은 사실이지만 변경된 행에서는 사용되지 않습니다.

Percona Server 5.5.28로 테스트 한 아래 데모를 참조하십시오. 구성 변수 innodb_autoinc_lock_mode=1(기본값) :

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 |
+----+------+

---

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.

---

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.

---

I routinely use INSERT IGNORE, and it sounds like exactly the kind of behavior you're looking for as well. As long as you know that rows which would cause index conflicts will not be inserted and you plan your program accordingly, it shouldn't cause any trouble.

---

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).

---

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.

---

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.

---

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.

---

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.

---

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

참고URL : https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update