on duplicate key UPDATE是防止新增时有重复数据而重复插入,如果有重复数据,就会更新那条重复的数据而不是新增一条;
前提是必须有【唯一索引】,它是根据唯一索引去判断是否有重复数据而去选择是【插入】还是【更新】
先新建数据表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
使用ON duplicate KEY UPDATE插入一条数据(其中values是使用插入的数据比较,可以自定义)
INSERT INTO test_db.`user`
(name, age)
VALUES('李张三', 15)
on duplicate key UPDATE
name = values(name),
age = values(age);
查询数据
id|name|age|
--|----|---|
1|李张三 | 15|
再使用相同的插入语句插入数据;会发现还是会插入数据而不是新增
这是因为我们在指定插入时没指定id这个唯一索引列(当前表中只有id有唯一索引)
id|name|age|
--|----|---|
1|李张三 | 15|
2|李张三 | 15|
所以,只需插入时指定id,如果id有重复,则更新数据,否则新增数据。
如果为【name】新增一条唯一索引,在下次不指定id,让id自增,插入新的数据时,如果name有重复数据,则更新数据,否则新增数据。