在 MySQL 中为现有数据生成 GUID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6280789/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generate GUID in MySQL for existing Data?
提问by Tom
I've just imported a bunch of data to a MySQL table and I have a column "GUID" that I want to basically fill down all existing rows with new and unique random GUID's.
我刚刚将一堆数据导入到 MySQL 表中，并且我有一列“GUID”，我想基本上用新的和唯一的随机 GUID 填充所有现有行。
How do I do this in MySQL ?
我如何在 MySQL 中做到这一点？
I tried
我试过
UPDATE db.tablename
SET columnID = UUID()
where columnID is not null
And just get every field the same
并且让每个字段都相同
采纳答案by a1ex07
I'm not sure if it's the easiest way, but it works. The idea is to create a trigger that does all work for you, then, to execute a query that updates your table, and finally to drop this trigger:
我不确定这是否是最简单的方法，但它有效。这个想法是创建一个为您完成所有工作的触发器，然后执行更新表的查询，最后删除此触发器：
delimiter //
create trigger beforeYourTableUpdate BEFORE UPDATE on YourTable
FOR EACH ROW
BEGIN
SET new.guid_column := (SELECT UUID());
END
//
Then execute
然后执行
UPDATE YourTable set guid_column = (SELECT UUID());
And DROP TRIGGER beforeYourTableUpdate;
和DROP TRIGGER beforeYourTableUpdate;
UPDATEAnother solution that doesn't use triggers, but requires primary key or unique index :
更新另一种不使用触发器但需要主键或唯一索引的解决方案：
UPDATE YourTable,
INNER JOIN (SELECT unique_col, UUID() as new_id FROM YourTable) new_data
ON (new_data.unique_col = YourTable.unique_col)
SET guid_column = new_data.new_id
UPDATEonce again:
It seems that your original query should also work (maybe you don't need WHERE columnID is not null, so all my fancy code is not needed.
再次更新：看来您的原始查询也应该有效（也许您不需要WHERE columnID is not null，所以不需要我所有花哨的代码。
回答by Rakesh Prajapati
I had a need to add a guid primary key column in an existing table and populate it with unique GUID's and this update query with inner select worked for me:
我需要在现有表中添加一个 guid 主键列，并用唯一的 GUID 填充它，这个带有内部选择的更新查询对我有用：
UPDATE sri_issued_quiz SET quiz_id=(SELECT uuid());
So simple :-)
很简单 ：-）
回答by Imran-UK
The approved solution does create unique IDs but on first glance they look identical, only the first few characters differ.
批准的解决方案确实创建了唯一的 ID，但乍一看它们看起来相同，只有前几个字符不同。
If you want visibly different keys, try this:
如果你想要明显不同的键，试试这个：
update CityPopCountry set id = (select md5(UUID()));
MySQL [imran@lenovo] {world}> select city, id from CityPopCountry limit 10;
+------------------------+----------------------------------+
| city | id |
+------------------------+----------------------------------+
| A Coru?a (La Coru?a) | c9f294a986a1a14f0fe68467769feec7 |
| Aachen | d6172223a472bdc5f25871427ba64e46 |
| Aalborg | 8d11bc300f203eb9cb7da7cb9204aa8f |
| Aba | 98aeeec8aa81a4064113764864114a99 |
| Abadan | 7aafe6bfe44b338f99021cbd24096302 |
| Abaetetuba | 9dd331c21b983c3a68d00ef6e5852bb5 |
| Abakan | e2206290ce91574bc26d0443ef50fc05 |
| Abbotsford | 50ca17be25d1d5c2ac6760e179b7fd15 |
| Abeokuta | ab026fa6238e2ab7ee0d76a1351f116f |
| Aberdeen | d85eef763393862e5fe318ca652eb16d |
+------------------------+----------------------------------+
I'm using MySQL Server version: 5.5.40-0+wheezy1 (Debian)
我正在使用 MySQL 服务器版本：5.5.40-0+wheezy1 (Debian)
回答by Brad Johnson
select @i:=uuid();
update some_table set guid = (@i:=uuid());
回答by enobrev
Just a minor addition to make as I ended up with a weird result when trying to modify the UUIDs as they were generated. I found the answerby Rakeshto be the simplest that worked well, except in cases where you want to strip the dashes.
只是一个小小的补充，因为我在尝试修改生成的 UUID 时得到了一个奇怪的结果。我找到了答案通过拉克什是最简单的运作良好，除了要剥去破折号案件。
For reference:
以供参考：
UPDATE some_table SET some_field=(SELECT uuid());
This worked perfectly on its own. But when I tried this:
这本身就很完美。但是当我尝试这样做时：
UPDATE some_table SET some_field=(REPLACE((SELECT uuid()), '-', ''));
Then all the resulting values were the same (not subtly different - I quadruple checked with a GROUP BY some_fieldquery). Doesn't matter how I situated the parentheses, the same thing happens.
然后所有结果值都相同（没有细微的不同 - 我通过GROUP BY some_field查询进行了四重检查）。无论我如何放置括号，都会发生同样的事情。
UPDATE some_table SET some_field=(REPLACE(SELECT uuid(), '-', ''));
It seems when surrounding the subquery to generate a UUID with REPLACE, it only runs the UUID query once, which probably makes perfect sense as an optimization to much smarter developers than I, but it didn't to me.
似乎在围绕子查询生成带有 REPLACE 的 UUID 时，它只运行一次 UUID 查询，这对于比我更聪明的开发人员来说可能是一种优化，但对我来说却没有。
To resolve this, I just split it into two queries:
为了解决这个问题，我只是将其拆分为两个查询：
UPDATE some_table SET some_field=(SELECT uuid());
UPDATE some_table SET some_field=REPLACE(some_field, '-', '');
Simple solution, obviously, but hopefully this will save someone the time that I just lost.
显然，简单的解决方案，但希望这可以节省我刚刚失去的时间。
回答by evan.leonard
Looks like a simple typo. Didn't you mean "...where columnId isnull"?
看起来像一个简单的错字。你的意思不是“...... columnId为空”吗？
UPDATE db.tablename
SET columnID = UUID()
where columnID is null
回答by Oleksandr Korniienko
I faced mostly the same issue. Im my case uuid is stored as BINARY(16) and has NOT NULL UNIQUE constraints. And i faced with the issue when the same UUID was generated for every row, and UNIQUE constraint does not allow this. So this query does not work:
我遇到了大部分相同的问题。我的情况 uuid 存储为 BINARY(16) 并且具有 NOT NULL UNIQUE 约束。当为每一行生成相同的 UUID 时，我遇到了这个问题，而 UNIQUE 约束不允许这样做。所以这个查询不起作用：
UNHEX(REPLACE(uuid(), '-', ''))
UNHEX(REPLACE(uuid(), '-', ''))
But for me it worked, when i used such a query with nested inner select:
但对我来说，当我使用带有嵌套内部选择的查询时，它起作用了：
UNHEX(REPLACE((SELECT uuid()), '-', ''))
UNHEX(REPLACE((SELECT uuid()), '-', ''))
Then is produced unique result for every entry.
然后为每个条目产生唯一的结果。
回答by Ashutosh Niranjan
UPDATE db.tablename SET columnID = (SELECT UUID()) where columnID is not null
回答by Leonardo Filipe
// UID Format: 30B9BE365FF011EA8F4C125FC56F0F50
UPDATE `events` SET `evt_uid` = (SELECT UPPER(REPLACE(@i:=UUID(),'-','')));
// UID Format: c915ec5a-5ff0-11ea-8f4c-125fc56f0f50
UPDATE `events` SET `evt_uid` = (SELECT UUID());
// UID Format: C915EC5a-5FF0-11EA-8F4C-125FC56F0F50
UPDATE `events` SET `evt_uid` = (SELECT UPPER(@i:=UUID()));
回答by Hugo R
MYsql UPDATE tablename SET columnName = UUID()
MYsql UPDATE 表名 SET columnName = UUID()
oracle UPDATE tablename SET columnName = SYS_GUID();
oracle UPDATE tablename SET columnName = SYS_GUID();
SQLSERVER UPDATE tablename SET columnName = NEWID();;
SQLSERVER 更新表名 SET columnName = NEWID();;
