在MySQL中以原子方式插入最低可能的唯一正整数 [英] Insert the lowest possible unique positve integer in an atomic fashion in MySql

问题描述

说我有一个带有唯一正整数字段的表.我目前有ID为[1、2、3、4、5、8、12、35]的行.我是否可以编写一个插入查询，将id字段分配给最低的唯一正整数(在这种情况下为6)?它将必须原子地执行此操作，这样就不会出现并发插入彼此消失或失败的可能性.

Say I have a table with a unique positive integer field. I currently have rows with id's of [1, 2, 3, 4, 5, 8, 12, 35]. Is there a insert query that I could write that would assign the id field to the lowest unique positive integer (in this case 6)? It would have to do this atomically so there isn't the possibility of concurrent inserts wiping out each other or failing.

推荐答案

我不会不使用它来填写缺少"的ID，但这应该可以工作:

I would not use this to fill up "missing" ids, but this should work:

Insert Into t (id)
  Select Coalesce( Min(t.id) + 1, 0 )
  From t
  Left Join t As t2 On ( t2.id = t.id + 1 )
  Where t2.id Is Null

获取所有id，其中id + 1不存在(Left Join)，并插入Min(id)+1或0(如果不可用).

Get all ids where id + 1 does not exist (Left Join), and insert Min(id)+1 or 0 if non is available.

这篇关于在MySQL中以原子方式插入最低可能的唯一正整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！