Skip to main content

第 26 篇 DML 语句之合并数据

imodouAbout 3 minMySQL

《MySQL 入门教程》第 26 篇 DML 语句之合并数据

上一篇我们介绍了如何使用 DELETE 语句删除数据，本篇继续学习 MySQL 中的数据合并操作，包括INSERT ON DUPLICATE KEY和REPLACE语句。

📝数据合并操作在 SQL 标准中使用 MERGE 语句实现，MySQL 使用专有的语法，有些数据库使用 UPSERT 语句。

26.1 INSERT ON DUPLICATE KEY

MySQL 中的 INSERT 语句提供了一个额外的子句：ON DUPLICATE KEY UPDATE，可以用于同时实现插入和更新操作。当插入的数据违反主键或者唯一约束时，执行更新操作替换原表中的数据。

INSERT INTO table_name(col1, col2, ...)
VALUES (val1, val2, ...)
    ON DUPLICATE KEY UPDATE
       col1 = expr1,
       col2 = expr2,
       ...;

INSERT INTO table_name(col1, col2, ...)
SELECT ...
    ON DUPLICATE KEY UPDATE
       col1 = expr1,
       col2 = expr2,
       ...;

其中，ON DUPLICATE KEY UPDATE 表示存在唯一值冲突时更新相应的字段数据；否则插入数据。

首先，创建一个测试表：

DROP TABLE IF EXISTS t;
CREATE TABLE t(id int NOT NULL PRIMARY KEY, val varchar(10));
INSERT INTO t VALUES (1, '一'),(2, '二'),(3, '三');

再次插入一条记录：

INSERT INTO t VALUES (1, '壹');
ERROR 1062 (23000): Duplicate entry '1' for key 't.PRIMARY'

由于 id = 1 已经存在，该语句违反了主键约束。此时，可以使用以下语句实现更新操作：

INSERT INTO t VALUES (1, '壹')
ON DUPLICATE KEY UPDATE
       val = '壹';

id|val|
--|---|
 1|壹  |
 2|二  |
 3|三  |

ON DUPLICATE KEY UPDATE 子句中的赋值表达式可以使用别名引用插入的数据值，例如：

INSERT INTO t VALUES (1, '壹'), (4, '肆') AS new(id, val)
ON DUPLICATE KEY UPDATE
       val = new.val;

SELECT * FROM t;
id|val|
--|---|
 1|壹  |
 2|二  |
 3|三  |
 4|肆  |

对于第二种语法形式，如果使用 UNION 操作指定源数据，需要将其定义为派生表。例如：

-- 错误语法
INSERT INTO t 
(SELECT 1 AS id, '壹' AS v
   UNION ALL
   SELECT 4, '肆')
ON DUPLICATE KEY UPDATE
val = v;

-- 正确语法
INSERT INTO t 
SELECT * FROM
  (SELECT 1 AS id, '壹' AS v
   UNION ALL
   SELECT 4, '肆') AS s
ON DUPLICATE KEY UPDATE
val = v;

如果表中存在多个唯一约束，可能会匹配到多行数据，但只会更新一条数据。因此，应该避免针对这种表使用 ON DUPLICATE KEY UPDATE 子句。

如果表中存在 AUTO_INCREMENT 字段，INSERT ... ON DUPLICATE KEY UPDATE 语句插入或者更新一行数据后，LAST_INSERT_ID() 函数将会返回 AUTO_INCREMENT 值。

26.2 REPLACE

MySQL 提供的另一种实现数据合并操作的语句是 REPLACE。REPLACE 语句会尝试插入数据，如果违反了主键或者唯一约束就删除原表中的数据并插入一条新记录。REPLACE 语法和 INSERT 非常类似：

REPLACE INTO table_name(col1, col2, ...)
 VALUES (val1, val2, ...);

REPLACE INTO table_name
    SET col1 = val1,
        col2 = val2,
        ...;

REPLACE INTO table_name(col1, col2, ...)
 SELECT ...;

如果表中不存在主键或者唯一约束，REPLACE 等价于 INSERT。上一节中的示例也可以用 REPLACE 语句改写如下：

REPLACE INTO t(id, val)
VALUES (1, '壹'), (4, '肆');

REPLACE INTO t
SET id = 1,
    val = '壹';

REPLACE INTO t 
SELECT * FROM
  (SELECT 1 AS id, '壹' AS v
   UNION ALL
   SELECT 4, '肆') AS s;

如果表中的主键或者唯一索引包含多个字段，需要所有索引字段的值都相同时 REPLACE 才会更新数据。例如：

CREATE TABLE test (
  id INT NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);
REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');

SELECT * FROM test;
id|data|ts                 |
--|----|-------------------|
 1|Old |2014-08-20 18:47:00|
 1|New |2014-08-20 18:47:42|

第二个 REPLACE 语句没有更新数据，而是插入了一条新的记录。

最后，如果想要实现数据插入，同时在违反唯一约束时不做任何操作，可以使用第 23 篇中介绍的INSERT IGNORE语句。

Default footer
Copyright © 2024 imodou