ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) without increa

Question

我正在运行一个SQL查询，看起来有点像这样

INSERT INTO people (Name, Role)
VALUES('{$Name}', '{$Occupation')
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

'people'表有一个代理ID键作为primary，这是由mysql的Autoindex设置的。

'Name'和'Role'组合成一个唯一键。

问题是，当我运行这个查询并命中具有相同名称和角色的人时，并获得他们各自的ID，自动索引仍然增加。

是否可以很容易地防止这种情况，或者我是否需要运行2个单独的查询来检查是否已经存在，然后插入?

Answer 1

首先，您不应该担心自动自增是否会增加。诚然，这在美学上是"丑陋的"，但序列中的间隔应该无关紧要。而且，这种差距可能是由其他原因造成的。

但是，如果这很重要，您可以通过检查查询来降低发生这种情况的概率:

INSERT INTO people (Name, Role)
    SELECT name, role
    FROM (SELECT '{$Name}' as name, '{$Occupation}' as role) x
    WHERE NOT EXISTS (SELECT 1 FROM people p WHERE p.name = x.name)
    ON DUPLICATE KEY UPDATE name = VALUES(name);  -- no op;

您仍然可以在可能出现间隙的地方获得竞争条件，但这使得这种可能性大大降低。

Answer 2

mysql文档中插入…重复键更新说:

例如，如果列a被声明为UNIQUE并且包含值1、以下两句语句效果相似:

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; 

(效果与一个InnoDB表，其中a是自动递增列。与一个自动递增列时，INSERT语句增加

所以，对于innodb表，这是预期的行为。要么为表选择不同的表引擎，要么如果不希望自动增量字段在更新时增加，就不能使用on duplicate key update子句。注意:在auto_increment id的序列中有间隙是没有错的。如果有连续递增序列号的业务需求，那么无论如何都不应该使用自动递增。

更新:

如果你担心很快耗尽bigint范围，那么使用uuid() mysql函数或类似的东西从php为你生成唯一的id。但是unsigned bigint的最大取值范围非常大