如果您指定一个ON DUPLICATE KEY UPDATE
子句并且要插入的行将导致UNIQUE
索引或中出现重复值,则会出现旧行PRIMARY
KEY
的 an 。UPDATE
例如,如果 columna
被声明为UNIQUE
并且包含 value
1
,则以下两个语句具有类似的效果:
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;
效果并不完全相同:对于一个
包含自增列的InnoDB
表,语句增加自增值但
不增加。
a
INSERT
UPDATE
如果 columnb
也是唯一的,
INSERT
则等效于此
UPDATE
语句:
UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2
匹配多行,则只
更新一行。通常,您应该尽量避免ON DUPLICATE KEY UPDATE
在具有多个唯一索引的表上使用子句。
对于ON DUPLICATE KEY UPDATE
,如果该行作为新行插入,则每行的受影响行值为 1,如果更新现有行,则为 2,如果现有行设置为其当前值,则为 0。如果
在连接到mysqldCLIENT_FOUND_ROWS
时将标志指定给
mysql_real_connect()
C API 函数,并且将现有行设置为其当前值,则受影响的行值为 1(而不是 0)。
如果表包含一AUTO_INCREMENT
列并INSERT
... ON DUPLICATE KEY UPDATE
插入或更新一行,该LAST_INSERT_ID()
函数将返回该AUTO_INCREMENT
值。
该ON DUPLICATE KEY UPDATE
子句可以包含多个列分配,以逗号分隔。
在子句中的赋值表达式中ON DUPLICATE KEY
UPDATE
,您可以使用
函数从语句部分
引用列值
。换句话说,
在子句中指的是将被插入的值,没有重复键冲突发生。此功能在多行插入中特别有用。该
函数仅在子句或
语句
中有意义,否则返回。例子:
VALUES(
col_name
)INSERT
INSERT ...
ON DUPLICATE KEY UPDATE
VALUES(
col_name
)ON DUPLICATE KEY UPDATE
col_name
VALUES()
ON DUPLICATE KEY UPDATE
INSERT
NULL
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
该语句与以下两个语句相同:
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
ON DUPLICATE KEY UPDATE c=9;
DELAYED
使用 时将忽略
该选项ON DUPLICATE KEY UPDATE
。
对于INSERT
... SELECT
语句,这些规则适用于SELECT
您可以在ON DUPLICATE KEY
UPDATE
子句中引用的可接受的查询表达式形式:
对单个表查询中的列的引用,该表可能是派生表。
对多个表的连接查询中的列的引用。
对查询中的列的引用
DISTINCT
。引用其他表中的列,只要
SELECT
不使用GROUP BY
. 一个副作用是您必须限定对非唯一列名的引用。
对列的引用
UNION
不能可靠地工作。要解决此限制,请将 重写
UNION
为派生表,以便可以将其行视为单表结果集。例如,此语句可能会产生不正确的结果:
INSERT INTO t1 (a, b)
SELECT c, d FROM t2
UNION
SELECT e, f FROM t3
ON DUPLICATE KEY UPDATE b = b + c;
UNION
相反,使用将 重写为派生表
的等效语句
:
INSERT INTO t1 (a, b)
SELECT * FROM
(SELECT c, d FROM t2
UNION
SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;
将查询重写为派生表的技术还支持从GROUP BY
查询中引用列。
因为
INSERT ...
SELECT
语句的结果取决于行的顺序,SELECT
并且不能始终保证此顺序,所以在记录
INSERT ...
SELECT ON DUPLICATE KEY UPDATE
源和副本的语句时可能会出现分歧。因此,
INSERT ...
SELECT ON DUPLICATE KEY UPDATE
语句被标记为对基于语句的复制不安全。使用基于语句的模式时,此类语句会在错误日志中产生警告,并在使用模式时使用基于行的格式写入二进制日志MIXED
。INSERT ...
ON DUPLICATE KEY UPDATE
针对具有多个唯一键或主键的表的
语句也被标记为不安全。(缺陷 #11765650,缺陷 #58637)
另见第 17.1.2.1 节,“基于语句和基于行的复制的优点和缺点”。
使用表级锁INSERT ... ON DUPLICATE KEY UPDATE
等存储引擎的分区表
会锁定更新分区键列的表的任何分区。MyISAM
(这不会发生在使用存储引擎的表中,例如InnoDB
使用行级锁定的表。)有关更多信息,请参阅
第 19.6.4 节,“分区和锁定”。