mysql当插入重复时更新的方法:
第一种方法:
示例一:插入多条记录
假设有一个主键为 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 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中。
第二种方法:
INSERT 中ON DUPLICATE KEY UPDATE的使用(本文重点)
如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:
- mysql>INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
- mysql>UPDATE table SET c=c+1 WHERE a=1;
如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:
- mysql>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。
您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT...UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT...UPDATE语句中有意义,其它时候会返回NULL。
示例:
- mysql>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
- ->ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
本语句与以下两个语句作用相同:
- mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)
- ->ON DUPLICATE KEY UPDATE c=3;
- mysql>INSERT INTO table (a,b,c) VALUES (4,5,6)
- ->ON DUPLICATE KEY UPDATE c=9;
当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。
第三种方法:
REPLACE语句
我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用DELETE语句删除原先的记录,然后再使用INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案,这就是REPLACE语句。使用REPLACE插入一条记录时,如果不重复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。
使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一,形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。
在使用REPLACE时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则REPLACE就和INSERT完全一样的。
在执行REPLACE后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了DELETE删除这条记录,然后再记录用INSERT来插入这条记录。如果返回的值大于2,那说明有多个唯一索引,有多条记录被删除和插入。
REPLACE的语法和INSERT非常的相似,如下面的REPLACE语句是插入或更新一条记录。
REPLACE INTO users (id,name,age) VALUES(123, '赵本山', 50);
插入多条记录:
REPLACE INTO users(id, name, age)
- VALUES(123, '赵本山', 50), (134,'Mary',15);
- REPLACE也可以使用SET语句
- REPLACE INTO users SET id = 123, name = '赵本山', age = 50;
上面曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。
CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);
假设table1中已经有了3条记录
a b c
1 1 1
2 2 2
3 3 3
下面我们使用REPLACE语句向table1中插入一条记录。
REPLACE INTO table1(a, b, c) VALUES(1,2,3);
返回的结果如下
Query OK, 4 rows affected (0.00 sec)
在table1中的记录如下
a b c
1 2 3
相关推荐
- 示例中,当再次尝试`INSERT`一个`a=3`的记录时,由于`b`字段在`ON DUPLICATE KEY UPDATE`部分被指定更新为'r5',所以原有记录的`b`字段被更新,而`c`字段保持不变。 - 对于`AUTO_INCREMENT`字段,`INSERT INTO ....
在MySQL中,`REPLACE INTO` 和 `INSERT INTO ON DUPLICATE KEY UPDATE` 是两种处理数据插入时冲突的方法,主要用于处理当试图插入一条记录时,若已有相同唯一键值的记录存在,应该如何操作的情况。 1. **REPLACE ...
当你在`INSERT`语句后添加`ON DUPLICATE KEY UPDATE`,如果新插入的行违反了唯一性约束,MySQL将执行指定的`UPDATE`操作。这里的“唯一性”指的是任何具有唯一索引或主键的列。例如,如果你有一个名为`consume_locus...
INSERT INTO ipstats VALUES('192.168.0.1', 1) ON DUPLICATE KEY UPDATE clicks=clicks+1; ``` 这将自动检测到`ip`列的唯一性约束,如果IP已存在,就更新`clicks`列,增加1次点击。 总结一下`ON DUPLICATE KEY ...
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; ``` 当尝试插入的记录 `(1, 2, 3)` 中的 `a` 值已存在于表中时,`ON DUPLICATE KEY ...
例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果: 代码如下:INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;UPDATE TABLE SET c=c+1 W
ON DUPLICATE KEY UPDATE` 和 `REPLACE INTO`。这两种方法都可以实现“合并”操作,即在保持唯一键约束的同时,对重复的数据进行更新。 首先,我们来看一下`INSERT INTO ... ON DUPLICATE KEY UPDATE` 语句。这个...
Replace Into 和 Insert Into On Duplicate Key Update 两种方法都可以实现批量更新,但它们之间有着很大的区别。 Replace Into 操作本质是对重复的记录先 delete 后 insert,如果更新的字段不全会将缺失的字段置为...
在MySQL数据库中,`ON DUPLICATE KEY UPDATE` 是一个非常实用的特性,它允许你在执行INSERT语句时,如果插入的数据违反了唯一性约束(即存在相同的唯一索引),则自动执行UPDATE操作来更新已有记录。这个功能在处理...
ON DUPLICATE KEY UPDATE` 是 MySQL 数据库中的一种高效操作,它结合了插入新记录和更新已有记录的功能。这个语句主要用于处理唯一性约束冲突,如 PRIMARY KEY 或 UNIQUE 索引的情况。当试图插入的记录与现有记录在...
在MySQL数据库中,如果你需要执行一个操作,即当数据存在时进行更新,如果不存在则进行插入,你可以使用`INSERT INTO...ON DUPLICATE KEY UPDATE`语句。这个功能强大的语句结合了`INSERT`和`UPDATE`操作,允许你在...
ON DUPLICATE KEY UPDATE`这两种语句。这两种SQL语句可以帮助我们高效地处理数据冲突,避免繁琐的循环操作,提高数据库操作的效率。 首先,我们来看`REPLACE`语句。`REPLACE`类似于`INSERT`,但它会在检测到主键或...
INSERT INTO table (a, c) VALUES (1, 3) ON DUPLICATE KEY UPDATE c = c + 1; UPDATE table SET c = c + 1 WHERE a = 1; ``` 如果新插入的行`a=1`已经存在,`c`的值会被加1。如果行作为新记录插入,`c`的值将是3。...
MySQL中的`ON DUPLICATE KEY UPDATE`语句是一个非常实用的功能,它允许你在插入新记录时检查是否存在冲突的唯一键。如果遇到冲突(即试图插入的键已经存在),它会更新已有记录而不是抛出错误。这在处理批量导入或...
INSERT INTO table_name (col1, col2, col3) VALUES (val1, val2, val3) ON DUPLICATE KEY UPDATE col1 = new_val1, col2 = new_val2, col3 = new_val3; 这个语句首先尝试插入数据,如果遇到唯一键冲突,就会执行...