mysql key duplicate

最新推荐文章于 2024-10-21 10:43:55 发布

原创最新推荐文章于 2024-10-21 10:43:55 发布 · 785 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql key duplicate

数据库专栏收录该内容

6 篇文章

订阅专栏

本文介绍MySQL中如何处理主键冲突问题，特别是使用INSERT...ON DUPLICATE KEY UPDATE语句进行数据更新的方法。同时讨论了last_insert_id()函数在不同情况下的行为。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL

insert 操作/主键冲突:Duplicate问题

在insert数据时在一个UNIQUE索引或PRIMARY KEY中出现重复值的问题：

可以在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。

创建测试表,准备数据
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` varchar(8) DEFAULT NULL,
`b` varchar(8) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES ('1', 'a', 'a');
INSERT INTO `test` VALUES ('2', 'b', 'b');
INSERT INTO `test` VALUES ('3', 'c', 'c');

1.-- 主键冲突:Duplicate entry '1' for key 'PRIMARY'
insert into test(id,a,b) values(1, 'm','m');

2.-- 主键冲突时:执行update操作
insert into test(id,a,b) values(1, 'm','m')
on duplicate key update
a = 'm',
b = 'm';

3.-- insert多条记录主键冲突时:执行update操作
insert into test(id,a,b) values(1, 'x','x'),(2, 'y','y')
on duplicate key update

a = VALUES(a), -- 只修改a字段

b = values(b); 修改b字段

如果一个表中包含了一个auto_increment的字段，每次insert数据后，可以通过last_insert_id()方法返回最后自动生成的值，如果通过INSERT ... ON DUPLICATE KEY UPDATE语句修改了一条数据，那么再通过last_insert_id()方法获取的值将不正确，实际测试中是多了一个数，比如向表中增加了3条数据，那么通过last_insert_id()方法得到的值是3，但是通过该语句修改了一条数据后，通过last_insert_id()方法得到的值是4。如果想解决该问题，可以通过如下语句：

<span style="font-family:Arial;font-size:18px;">INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;</span>

重点是这句id=LAST_INSERT_ID(id)。解决id增长问题

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！