不使用自增的唯一标识符

Question

不使用自增的唯一标识符

3

我有一个已存在的模式，其中包含一个非自增主键。此主键被用作其他十几个表中的外键。

我继承了一个存在严重性能问题的程序。当前情况下，当向该表添加新行时，会创建一个新的唯一 ID，方法如下：

1) a query for all existing primary key values is retrieved
2) a random number is generated
3) if the number does not exist in the retrieved values, use it, otherwise goto (2)

该应用程序是多线程和多服务器的，因此仅在启动时抓取现有ID不是一个选项。我没有来自初始请求的唯一信息可供获取并转换为伪唯一值（如成员ID）。

我了解理论上对内部进行手术以添加自动递增到现有的主键是可能的。我也了解到可以系统性地删除所有指向此表的外键，然后创建-重命名-插入表的新版本，然后再添加回外键，但是此表格式由第三方应用程序规定，如果我搞砸了就会发生糟糕的事情。

是否有一种方法可以利用sql/mysql来生成唯一的行值？

我想到的最接近的方法是从大空间中随机选择一个数字，希望它在数据库中是唯一的，当偶尔发生冲突时则重试。

有什么建议吗？

- Matt Thompson

使用MySQL的UUID()生成唯一标识符可行吗？或者必须是数字？ - Michael Berkowski

现有列的数据类型是什么？假设它是“int”，无需删除现有键，只需更改表并将字段设置为“auto_increment”。http://sqlfiddle.com/#!2/df7c1/1 - sgeddes

你可以获取到目前使用的最高数字，然后让该列从下一个数字开始自动递增。 - radar

该列是主键，用作外键的参考。似乎我无法简单地修改它而不出现错误，例如“错误代码：1833. 无法更改列'id'：已在外键约束中使用”。 - Matt Thompson

这个主键列的数据类型是什么？ - Michael - sqlbot

4个回答

0

我觉得你可以选择MAX('strange-id-column') + 1。这个值将是唯一的，你可以将这个SQL代码放在一个事务中，与INSERT代码一起使用，以防止错误发生。

- Rola

4

不是一个好主意，因为它容易出现竞争条件。两个独立的交易可能都尝试生成相同的新ID值。 - Bill Karwin

我同意Bill Karwin的观点。虽然你可以执行SELECT(MAX(id)+1)...但是这个值返回后是唯一的，这并不正确。(至少，在多线程环境中，并不正确，除非添加一些破坏并发的锁定机制。) - spencer7593

是的，我的错误，我是指事务和锁定。但我认为这个SQL回答了问题。 - Rola

0

对于大型数据集，获取所有主键值列表并生成伪随机值进行验证是否唯一，这看起来非常昂贵。

我认为这种方法的一个大问题是，当使用相同的种子值启动序列时，伪随机数生成器将生成相同的值序列。

如果发生这种情况，那么就会不断出现冲突，直到序列达到尚未使用的值。下次再发生这种情况时，您需要再次遍历整个列表，以添加一个新值。

我不明白为什么这个值必须是随机的。

如果不需要伪随机性，而升序值可以接受的话，如果我不想对现有表进行任何更改，我会做以下事情：

我会创建另一个具有自动递增列的“id-generator”表。我执行插入到该表以生成id值。

我不会运行查询来检索现有表中的所有现有id值，而是会执行INSERT到“id-generator”表中，然后使用SELECT LAST_INSERT_ID()检索刚刚插入的行的id，并将其用作“生成”的id值。

基本上，模拟Oracle SEQUENCE对象。不必保留“id-generator”表中的所有行。因此，我可以删除所有id值小于最大id值的行。

如果有伪随机性（令人发抖）的要求，我可能会尝试使用INSERT来查找键是否存在。如果由于重复键而插入失败，则会尝试使用不同的id值再次尝试。

来自伪随机生成器的重复序列让我感到害怕...如果我连续得到几个碰撞，这些是来自先前使用过的序列还是来自不同序列的值。我没有任何办法知道。如果使用了已经使用过的种子，则放弃该序列并重新启动，我将追逐另一系列先前生成的值。

- spencer7593

我同意这很贵 - 贵得离谱。我一次只解决一个问题，但其他表中也在做同样的事情，但是使用varchar键！太糟糕了。值不必是随机的 - 那只是一个想法 - 类似于穷人的哈希函数。id生成器表很有趣，看起来可以工作，只要它在事务中。 - Matt Thompson

@Matt：我是想提到关于事务的问题。获取id的步骤不一定需要在事务的上下文中进行。因此，MyISAM表可以使用。 - spencer7593

@MattThompson：修改现有表中的列为AUTO_INCREMENT是另一种选择...我没有建议这样做，因为这个更改（虽然看起来更简单）实际上需要更多的调查和应用程序更改...我怀疑“生成id”过程的实施（部分原因）是为了解决“我不知道刚插入的行的id”问题。替换“生成唯一id”的函数实际上是一个更简单的方法。 - spencer7593

0

对于低并发水平（平均并发进行中的插入 < 1），您可以使用乐观锁定来实现唯一标识而无需自动增量：

为此功能设置一个单行表，例如：

create table last_id (last_id bigint not null default 0);

要获取下一个 ID，请在应用程序代码中检索此值，应用新的 ID 函数，然后尝试更新该值，例如：

select last_id from last_id; // In DB
newId = lastId + 1 // In app code
update last_id set last_id=$newId where last_id=$lastId // In DB

检查已更新的行数。如果为0，则表示另一个服务器已经完成了操作，您应该返回到步骤1。

- jazmit

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

如果表中有一个未被外键引用使用的主键，则删除该主键。目标是将列设置为自动增量主键。

因此，查找最大值，然后以下内容应该做你想要的：

alter table t modify id int not null auto_increment primary key;
alter table t auto_increment = <maximum value> + 1;

我认为明确设置auto_increment值并不是必要的，但我喜欢保险起见。