方法一:INSERT ON DUPLICATE KEY UPDATE
如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE
注意:
- 如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
- 如果该表中,由多个唯一索引,需要特别注意,出现重复时则该语句只能更新其中一行记录
如:原有数据表中的索引:UNIQUE KEY
uk_admin_name(
admin_name) USING BTREE
UNIQUE KEY
uk_password(
password) USING BTREE
执行:
1 | insert into tab_admin(`admin_name`, `password`) value('admin', 'admin888') on duplicate key |
从上图可以看出:虽然admin,admin888和两行都匹配上了,但是只更新了一行数据,受影响的行: 2
至于为啥优先选择更新:admin_name这个唯一键? 原因:他会按照键的顺序来更新
结论:
- 如果有多个唯一建存在,则这样的插入相当于:(其中a,b都是唯一键)
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。 - DUPLICATE 插入多行数据。您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。
1
INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE `c`=VALUES(`a`)+VALUES(`b`);
本语句与以下两个语句作用相同:
1 | INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=3; |
注释:当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。
举个例子,字段a被定义为UNIQUE,并且原数据库表table中已存在记录(2,2,9)和(3,2,1),如果插入记录的a值与原有记录重复,则更新原有记录,否则插入新行:
1 | INSERT INTO TABLE (a,b,c) VALUES |
结果:
发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突,则执行ON DUPLICATE KEY UPDATE,将原有记录(2,2,9)更新成(2,5,9),将(3,2,1)更新成(3,3,1),插入新记录(1,2,3)和(4,8,2)
影响的行数为:6行
待确认:
更新值的时候,像这种create_time根据数据库时间变动的,不会自动更新时间,还是最初插入的时间,需要构造数据的时候,把create_time自己加上。
方法二:REPLACE INTO
原理:插入数据时,如果发现了重复记录,则系统自动先调用了DELETE删除这条记录,然后再用INSERT来插入这条记录,影响的行数为2行,如果没有发现,则直接插入,影响的行数是1行
注意:REPLACE
和INSERT ON DUPLICATE
的区别,在于REPLACE会影响多条结果。比如在表中有超过一个的唯一索引。在这种情况下,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条记录
1 | a b c |
下面我们使用REPLACE语句向table1中插入一条记录。
1 | REPLACE INTO table1(a, b, c) VALUES(1,2,3); |
在table1中的记录如下
1 | a b c |
我们可以看到,REPLACE将原先的3条记录都删除了,然后将(1, 2, 3)插入。
方法三:使用ignore关键字
原理:如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用:
1 | INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '9999'); |
这样当有重复记录就会忽略,执行后返回受影响的行数为0
又如复制表:
1 | INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`; |
注意:ignore只提供不插入重复的记录,但是不会更新记录,replace和duplicate可以实现更新记录。
关于唯一键的注意:
1:在MYSQL中UNIQUE索引将会对null字段失效,也就是说(a字段上建立唯一索引):
1 | INSERT INTO `test` (`a`) VALUES (NULL); |
是可以重复插入的(联合唯一索引也一样)
方法四:insert … select … where not exist
根据select的条件判断是否插入,可以不光通过primary 和unique来判断,也可通过其它条件。例如:
1 | INSERT INTO books (name) SELECT 'MySQL Manual' FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1) |