使用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：
- Try to insert the new row into the table
- 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修改成了不同的值
