MySql避免重复插入记录方法(ignore,Replace,ON DUPLICATE KEY UPDATE)

Posted by ZhangShun Blog on April 1, 2019

使用ignore关键字

Several statements in MySQL support an optional IGNORE keyword. This keyword causes the server to downgrade certain types of errors and generate warnings instead. For a multiple-row statement, IGNORE causes the statement to skip to the next row instead of aborting.

For example, if the table t has a primary key column i, attempting to insert the same value of i into multiple rows normally produces a duplicate-key error:

1
2
mysql> INSERT INTO t (i) VALUES(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

With IGNORE, the row containing the duplicate key still is not inserted, but a warning occurs instead of an error:

1
2
3
4
5
6
7
8
9
10
11
mysql> INSERT IGNORE INTO t (i) VALUES(1),(1);
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

多数用于防止重复插入记录导致主键冲突

Replace

To use REPLACE, you must have both the INSERT and DELETE privileges for the table.

MySQL uses the following algorithm for REPLACE:

  1. Try to insert the new row into the table
  2. While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
    • Delete from the table the conflicting row that has the duplicate key value
    • Try again to insert the new row into the table

create table

1
2
3
4
5
6
CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

When we create this table and run the statements shown in the mysql client, the result is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

Now we create a second table almost identical to the first, except that the primary key now covers 2 columns, as shown here (emphasized text):

1
2
3
4
5
6
CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);

When we run on test2 the same two REPLACE statements as we did on the original test table, we obtain a different result:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

使用Replace时确保表中只有一个PRIMARY KEY或UNIQUE索引,否则使用一个REPLACE语句没有意义。该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行。

ON DUPLICATE KEY UPDATE

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。

1
2
3
4
5
6
7
8
INSERT INTO user_admin_t (_id,password) 
VALUES ('1','第一次插入的密码') 
ON DUPLICATE KEY UPDATE 
_id = 'UpId',
password = 'upPassword';

如果_id字段存在'1',会产生主键冲突错误,此时上面sql相当于执行了:
update user_admin_t set _id = 'UpId',password = 'upPassword' where _id='1';

那么问题又来了,有人会说ON DUPLICATE KEY UPDATE 后面跟的是固定的值,如果我想要分别给不同的记录插入不同的值怎么办呢?

1
2
3
4
5
6
INSERT INTO user_admin_t (_id,password) 
VALUES 
('1','多条插入1') ,
('UpId','多条插入2')
ON DUPLICATE KEY UPDATE 
password =  VALUES(password);

VALUES(password)成功的为不同id的password修改成了不同的值