MYSQL中无重复插入更新几种方法

作者：袖梨 2022-11-14

第一种解决方案：

如果你指定了ON DUPLICATE KEY UPDATE命令语句，那么在唯一索引或者主索引的作用下将不插入与数据库记录重复的内容，但同时会更新数据库中的旧记录。例如，字段a被声明为唯一索引并且里面只包含有值为1的记录，以下两个语句会达到同样的效果：

代码如下

复制代码

一、INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

二、UPDATE table SET c=c+1 WHERE a=1;

一、INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

二、UPDATE table SET c=c+1 WHERE a=1;

受影响的是a=1的行，当插入时c的值加1。
如果字段b也是唯一的话，这个插入语句将和以下语句的效果一样：
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2匹配了不止一行，也只是第一行被更新。一般地，如果表中有多个唯一索引的话，你应该避免在使用用ON DUPLICATE KEY子句。
你可以在插入更新语句 INSERT … UPDATE 中使用 VALUES(字段名) 函数去关联某一行记录。也就是说， VALUES(字段名) 可以用在UPDATE语句中去更新某字段的值而不会出现重复键。这个函数在多行插入中尤其有用。但是函数 VALUES() 仅当用在 INSERT … UPDATE 语句中才有意义，否则会返回NULL。例如：

代码如下

复制代码

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

这个语句和下面两个是同效果的：

代码如下

复制代码

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
ON DUPLICATE KEY UPDATE c=9;

如果表中包含有一个自动递增字段AUTO_INCREMENT，并用 INSERT … UPDATE 插入一行，函数 LAST_INSERT_ID()会返回AUTO_INCREMENT的值，如果这个语句更新某一行， LAST_INSERT_ID() 就没有意义了。但是，你可以通过用 LAST_INSERT_ID(expr)使它变得有意义，假如id字段是自动递增栏的话，使 LAST_INSERT_ID() 对更新语句有意义的方法如下：

代码如下

复制代码

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

如果你使用 ON DUPLICATE KEY UPDATE 语句的话，延迟执行选项 DELAYED 将被忽略。

第二种解决方案

这种解决方案比较通用，不过个人感觉性能不是很好（没有测试）
示例一：插入多条记录
假设有一个主键为 client_id 的 clients 表，可以使用下面的语句：

代码如下

复制代码

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

示例二：插入单条记录

代码如下

复制代码

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值，即使这些值还不存在当前表中。

第三种解决方案

REPLACE语法

replace的语法格式为：

代码如下

复制代码

1. replace into table_name(col_name, …) values(…)

2. replace into table_name(col_name, …) select …

3. replace into table_name set col_name=value, …

算法说明：

REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值，则在新记录被插入之前，旧记录被删除，即：

1. 尝试把新行插入到表中