如何为mysql表中的新列中的每一行生成唯一值？

This seems conceptually simple, but once I put it in SQL terminology, it got difficult.

这看起来在概念上很简单,但是一旦我把它放在SQL术语中,就很难了。

Say one has 3 rows in a table called "stuff".

假设一个表中有3行称为“stuff”。

 col1     col2     col3
 ------   ------   ------
 aaaa     bbbb     cccc
 xxxx     yyyy     zzzz
 aaaa     bbbb     cccc

Suppose I want to add a pk column and give it unique values. There is no SQL that I can give in an update that will update the 1st and not the 3rd row. But it turns out that an extension to SQL that MySQL allows gives an answer.

假设我想添加一个pk列并为其赋予唯一值。在更新中没有SQL可以提供更新第1行而不是第3行。但事实证明,MySQL允许的SQL扩展提供了答案。

 alter table stuff add column pk int;
 update table set pk = 1 where pk is NULL limit 1;
 update table set pk = 2 where pk is NULL limit 1;
 update table set pk = 3 where pk is NULL limit 1;

This gives me:

这给了我:

 pk       col1     col2     col3
 ------   ------   ------   ------
 1        aaaa     bbbb     cccc
 2        xxxx     yyyy     zzzz
 3        aaaa     bbbb     cccc

But it turns out that if you do this with a table of over 5 million rows, it is taking an extremely long time. I have a guess that it is doing a bunch of work to find all the rows with a pk = NULL, when what I really ever want is just one.

但事实证明,如果你使用超过500万行的表来执行此操作,则需要花费很长时间。我猜测它正在做一堆工作来找到所有pk = NULL的行,而我真正想要的只是一个。

Does anyone know why this would run slowly? Is there a faster or more SQL-compatible way to do this? I cannot think what it would be.

有谁知道为什么这会慢慢运行?是否有更快或更多SQL兼容的方法来做到这一点?我想不出它会是什么。

1 个解决方案

#1

Your query is slow because your IS NULL condition will produce FULL SCAN each time - and that is because, obviously, you can't still use index on the column (you have not it yet)

你的查询很慢,因为你的IS NULL条件每次都会产生FULL SCAN - 这是因为,显然,你仍然不能在列上使用索引(你还没有)

You can use MySQL variables to generate your unique values. That will be:

您可以使用MySQL变量生成唯一值。那将是:

UPDATE stuff CROSS JOIN (SELECT @pk:=0) AS init SET stuff.pk=@pk:=@pk+1

#1