INSERT ... ON DUPLICATE KEY UPDATE语句
如果您指定一个ON DUPLICATE KEY UPDATE
子句,并且要插入的行将导致UNIQUE
索引或中的值重复PRIMARY KEY
,UPDATE
则会出现旧行中的。例如,如果将column a
声明为UNIQUE
并包含value 1
,则以下两个语句具有相似的作用:
INSERT INTO t1 (a,b,c)VALUES (1,2,3)ON DUPLICATE KEY UPDATE c=c+1;UPDATE t1SET c=c+1WHERE a=1;
(对于具有自动递增列的InnoDB
表,其效果并不相同a
。对于自动递增列,一条INSERT
语句会增加自动递增值,但UPDATE
不会。)
如果column b
也是唯一的,INSERT
则等效于以下UPDATE
语句:
UPDATE t1SET c=c+1WHERE a=1 OR b=2LIMIT 1;
如果a=1 OR b=2
匹配多个行,只有一个行被更新。通常,您应该尝试避免ON DUPLICATE KEY UPDATE
在具有多个唯一索引的表上使用子句。
使用ON DUPLICATE KEY UPDATE
,如果将行作为新行插入,则每行的受影响行值为1;如果更新了现有行,则为2;如果将现有行设置为其当前值,则为0。如果在连接到mysqld时CLIENT_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;
注意
VALUES()
从MySQL 8.0.20开始不推荐使用来引用新的行和列,并且在将来的MySQL版本中可能会删除该引用。而是使用行和列别名,如本节以下几节所述。
从MySQL 8.0.19开始,可以在行中使用别名,或者在VALUES
or SET
子句之后,并在AS
关键字之后添加一个或多个要插入的列。使用row别名new
,VALUES()
可以以如下所示的形式编写先前显示的用于访问新列值的语句:
INSERT INTO t1 (a,b,c)VALUES (1,2,3),(4,5,6)AS new ON DUPLICATE KEY UPDATE c =new .a+new .b;
另外,如果您使用列别名m
,n
和p
,则可以在赋值子句中省略行别名,并编写相同的语句,如下所示:
INSERT INTO t1 (a,b,c)VALUES (1,2,3),(4,5,6)AS new (m,n,p)ON DUPLICATE KEY UPDATE c = m+n;
以这种方式使用列别名时VALUES
,即使未在赋值子句中直接使用它,您仍必须在该子句后使用行别名。
SET
如前所述,还可以将行和列别名与子句一起使用。可以像下面显示的那样使用SET
而不是仅VALUES
在两个INSERT ... ON DUPLICATE KEY UPDATE
语句中使用:
INSERT INTO t1SET a=1,b=2,c=3AS new ON DUPLICATE KEY UPDATE c =new .a+new .b;INSERT INTO t1SET a=1,b=2,c=3AS new (m,n,p)ON DUPLICATE KEY UPDATE c = m+n;
行别名不能与表名相同。如果未使用列别名,或者它们与列名相同,则必须使用ON DUPLICATE KEY UPDATE
子句中的行别名来区分它们。列别名就其所适用的行别名而言必须是唯一的(也就是说,引用同一行的列的列别名不能相同)。
对于INSERT ... SELECT
语句,这些规则适用于SELECT
您可以在ON DUPLICATE KEY UPDATE
子句中引用的可接受的查询表达式形式:
- 对单个表(可能是派生表)上查询的列的引用。
- 对多个表上的联接的查询中的列的引用。
- 对
DISTINCT
查询列的引用。 - 只要
SELECT
不使用,就引用其他表中的列GROUP BY
。副作用是您必须限定对非唯一列名的引用。
UNION
不支持从中引用列。要解决此限制,请将重写UNION
为派生表,以便可以将其行视为单表结果集。例如,以下语句产生错误:
INSERT INTO t1 (a, b)SELECT c, dFROM t2UNION SELECT e, fFROM t3ON DUPLICATE KEY UPDATE b = b + c;
相反,请使用等效语句将重写UNION
为派生表:
INSERT INTO t1 (a, b)SELECT *FROM (SELECT c, dFROM t2UNION SELECT e, fFROM t3)AS dtON 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)
另请参见“复制格式”。