在MySQL中使用“replace into”和“Insert into … on duplicate key update …”

有时，当我们想在包含重复键的表中插入行时，我们考虑在重复键更新时使用“替换为”或“插入...”。在最近的工作中，我发现使用它们时会遇到一些陷阱，而本文旨在说明会发生什么以及为什么发生。

 

1）replace into

 

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE `t1` (     `a` int(11) NOT NULL AUTO_INCREMENT,     `b` int(11) DEFAULT NULL,     `c` int(11) DEFAULT NULL,     PRIMARY KEY (`a`),     UNIQUE KEY `uniq_b` (`b`)   ) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

 

替换重复的行：

 

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from t1;    +---+------+------+   | a | b    | c    |   +---+------+------+   | 1 |    1 |    2 |   | 2 |    2 | NULL |   +---+------+------+

 

 

1 2 3

mysql> replace into t1(b,c) values (1,9);    Query OK, 2 rows affected (0.07 sec)

 

 

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from t1;   +---+------+------+   | a | b    | c    |   +---+------+------+   | 2 |    2 | NULL |   | 3 |    1 |    9 |   +---+------+------+

 

在唯一键上使用“替换为...”之后，删除了（1,1,2）并插入了新行（3,1,9），它不仅是替换（1,1,2）的值）行，因为列“ a”使用了AUTO_INCREMENT值“ 3”。

 

必须指定列名：

如果未指定列名，例如：

 

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from t1;   +---+------+------+   | a | b    | c    |   +---+------+------+   | 1 |    1 |    2 |   | 2 |    2 | NULL |   +---+------+------+

 

 

1 2 3

mysql> replace into t1(b) values (1);   Query OK, 2 rows affected (0.06 sec)

 

 

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from t1;   +---+------+------+   | a | b    | c    |   +---+------+------+   | 2 |    2 | NULL |   | 3 |    1 | NULL |   +---+------+------+

 

列'a'和'c'已更改为'3'和'null'，因此当使用'replace into'时，它将删除重复的行并插入具有默认值的新行 。

 

同时替换重复的主键和重复的唯一键：

 

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from t1;   +---+------+------+   | a | b    | c    |   +---+------+------+   | 2 |    2 |    2 |   | 3 |    1 |    2 |   +---+------+------+

 

 

1 2 3

mysql> replace into t1(a,b,c) values (2,1,9);   Query OK, 3 rows affected (0.10 sec)

 

 

1 2 3 4 5 6 7 8 9 10 11

mysql> select * from t1;   +---+------+------+   | a | b    | c    |   +---+------+------+   | 2 |    1 |    9 |   +---+------+------+

 

在“替换为...”之后，它显示“受影响的三行 ”，两行已被删除，仅一行插入到t1中。

如前所述，“替换为..”将在插入之前删除重复的行，因此，当“替换为t1（a，b，c）的值（2,1,9）;”时，在执行时，它首先检查主键，将其复制，因此（2,2,2）被删除，然后列'b'也被复制，因此（3,1,2）被删除，之后，没有重复的键，然后插入新行（2,1,9）。

 

2）Insert into … on duplicate key update …

使用同一表t1进行测试：

插入重复的主键行：

 

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from t1;    +---+------+------+   | a | b    | c    |   +---+------+------+   | 1 |    1 |    2 |   | 2 |    2 | NULL |   +---+------+------+

 

 

1 2 3

mysql> insert into t1(a,b,c) values (2,3,4) on duplicate key update c=4;   Query OK, 2 rows affected (0.02 sec)

 

 

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from t1;   +---+------+------+   | a | b    | c    |   +---+------+------+   | 1 |    1 |    2 |   | 2 |    2 |    4 |   +---+------+------+

 

“在重复键更新中插入...”只是检查了主键，发现主键“ a”具有重复值“ 2”之后，便将列“ c”更新为新值“ 4”，并被忽略了。同样重复的“ b”列。所以我们得到了新行（2,2,4）而不是（2,3,4）。

 

在重复的键行上插入：

 

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from t1;   +---+------+------+   | a | b    | c    |   +---+------+------+   | 1 |    1 |    2 |   | 2 |    2 |    4 |   +---+------+------+

 

 

1 2 3

mysql> insert into t1(b,c) values (1,5) on duplicate key update c=5;   Query OK, 2 rows affected (0.20 sec)

 

 

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from t1;   +---+------+------+   | a | b    | c    |   +---+------+------+   | 1 |    1 |    5 |   | 2 |    2 |    4 |   +---+------+------+

 

这与“替换为..”不同，它只是更新重复行上的值，而没有删除。

简而言之，在对重复键更新使用“替换为...”和“插入...”之前，您必须知道它们的实际作用，否则会得到意想不到的结果。