ON DUPLICATE KEY UPDATE ...如何更新AUTO INCREMENT？

CREATE TABLE `feeds` (
 `FEED_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `P_ID` bigint(20) NOT NULL,
 `P_NAME` varchar(100) NOT NULL,
 PRIMARY KEY (`P_ID`),
 UNIQUE KEY `FEED_ID` (`FEED_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

::

INSERT INTO `table_a` (`P_ID`,`P_NAME`) VALUES ('30','John') 
ON DUPLICATE KEY UPDATE `P_ID`= VALUES(`P_ID`),`P_NAME`= VALUES(`P_NAME`);

I would like to use INSERT.. ON DUPLICATE KEY UPDATE where anytime I create that insert, I'd like the FEED_ID to auto increment. As for now it stays the same because there was no new row insertion...

我想使用INSERT .. ON DUPLICATE KEY UPDATE,无论何时我创建该插入,我都希望FEED_ID自动增加。至于它现在保持不变,因为没有新的行插入......

Should I delete then insert?

我应该删除然后插入?

1 个解决方案

#1

It would seem like you're looking for REPLACE instead of INSERT ... ON DUPLICATE KEY; it will remove the old row if one exists with a conflicting primary key or unique key, and insert a new one with a new FEED_ID;

看起来你正在寻找REPLACE而不是INSERT ...在DUPLICATE KEY;如果存在具有冲突的主键或唯一键的旧行,它将删除旧行,并插入具有新FEED_ID的新行;

REPLACE INTO feeds (p_id,p_name) VALUES (1,'Olle');

An SQLfiddle to test with.

一个要测试的SQLfiddle。