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

文章目录26.1 INSERT ON DUPLICATE KEY26.2 REPLACE

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

?数据合并操作在 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语句。

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

不剪发的Tony老师

《MySQL 入门教程》第 26 篇 DML 语句之合并数据
CSDN认证博客专家


数据库架构师

毕业于北京航空航天大学,十多年数据库管理与开发经验,拥有OCP以及RHCE证书。目前在一家全球性的游戏公司从事数据库架构设计和开发,CSDN学院签约讲师以及GitChat专栏作者。

关注博主即可阅读全文


原创:https://www.panoramacn.com
源码网提供WordPress源码,帝国CMS源码discuz源码,微信小程序,小说源码,杰奇源码,thinkphp源码,ecshop模板源码,微擎模板源码,dede源码,织梦源码等。

专业搭建小说网站,小说程序,杰奇系列,微信小说系列,app系列小说

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

免责声明,若由于商用引起版权纠纷,一切责任均由使用者承担。

您必须遵守我们的协议,如果您下载了该资源行为将被视为对《免责声明》全部内容的认可-> 联系客服 投诉资源
www.panoramacn.com资源全部来自互联网收集,仅供用于学习和交流,请勿用于商业用途。如有侵权、不妥之处,请联系站长并出示版权证明以便删除。 敬请谅解! 侵权删帖/违法举报/投稿等事物联系邮箱:2640602276@qq.com
未经允许不得转载:书荒源码源码网每日更新网站源码模板! » 《MySQL 入门教程》第 26 篇 DML 语句之合并数据
关注我们小说电影免费看
关注我们,获取更多的全网素材资源,有趣有料!
120000+人已关注
分享到:
赞(0) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

您的打赏就是我分享的动力!

支付宝扫一扫打赏

微信扫一扫打赏