在 MySQL 中为现有数据生成 GUID？

发布于 2024-11-14 15:07:33 字数 227 浏览 2 评论 0原文

我刚刚将一堆数据导入到 MySQL 表中，并且有一列“GUID”，我想用新的且唯一的随机 GUID 基本上填充所有现有行。

我如何在 MySQL 中执行此操作？

我尝试过

UPDATE db.tablename
  SET columnID = UUID()
  where columnID is not null

，只是让每个字段都相同

原文

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.

How do I do this in MySQL ?

I tried

UPDATE db.tablename
  SET columnID = UUID()
  where columnID is not null

And just get every field the same

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

Spring初心 2024-11-21 15:07:33

我需要在现有表中添加一个 guid 主键列，并用唯一的 GUID 填充它，这个带有内部选择的更新查询对我有用：

UPDATE sri_issued_quiz SET quiz_id=(SELECT uuid());

很简单:-)

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:

UPDATE sri_issued_quiz SET quiz_id=(SELECT uuid());

So simple :-)

回复收藏 0 原文

薄荷→糖丶微凉 2024-11-21 15:07:33

我不确定这是否是最简单的方法，但它确实有效。我们的想法是创建一个为您完成所有工作的触发器，然后执行更新表的查询，最后删除此触发器：

delimiter //
create trigger beforeYourTableUpdate  BEFORE UPDATE on YourTable
FOR EACH ROW
BEGIN
  SET new.guid_column := (SELECT UUID());
END
//

然后执行

UPDATE YourTable set guid_column = (SELECT UUID());

And DROP TRIGGER beforeYourTableUpdate;

更新
另一种解决方案不使用触发器，但需要主键或唯一索引：

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

再次更新：
看来您的原始查询也应该有效（也许您不需要 WHERE columnID is not null，因此不需要我所有的花哨代码。

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;

UPDATE
Another 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

UPDATE once 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.

回复收藏 0 原文

毁梦 2024-11-21 15:07:33

批准的解决方案确实创建了唯一的 ID，但乍一看它们看起来相同，只有前几个字符不同。

如果您想要明显不同的密钥，请尝试以下操作：

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 |
+------------------------+----------------------------------+

我正在使用 MySQL 服务器版本：5.5.40-0+wheezy1 (Debian)

The approved solution does create unique IDs but on first glance they look identical, only the first few characters differ.

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)

回复收藏 0 原文

仅此而已 2024-11-21 15:07:33

select @i:=uuid();
update some_table set guid = (@i:=uuid());

select @i:=uuid();
update some_table set guid = (@i:=uuid());

回复收藏 0 原文

╭⌒浅淡时光〆 2024-11-21 15:07:33

只是做了一个小小的补充，因为当我尝试修改生成的 UUID 时，最终得到了一个奇怪的结果。我找到了答案，作者是 Rakesh 是最简单的，效果很好，除非你想去掉破折号。

仅供参考：

UPDATE some_table SET some_field=(SELECT uuid());

这本身就非常有效。但是当我尝试这样做时：

UPDATE some_table SET some_field=(REPLACE((SELECT uuid()), '-', ''));

然后所有结果值都是相同的（没有细微的不同 - 我使用 GROUP BY some_field 查询进行了四次检查）。不管我如何放置括号，都会发生同样的事情。

UPDATE some_table SET some_field=(REPLACE(SELECT uuid(), '-', ''));

似乎当围绕子查询使用 REPLACE 生成 UUID 时，它只运行 UUID 查询一次，这对于比我聪明得多的开发人员来说可能是一种优化，但对我来说却没有。

为了解决这个问题，我只是将其分为两个查询：

UPDATE some_table SET some_field=(SELECT uuid());
UPDATE some_table SET some_field=REPLACE(some_field, '-', '');

显然，简单的解决方案，但希望这会节省我刚刚失去的时间。

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 answer by Rakesh to be the simplest that worked well, except in cases where you want to strip the dashes.

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_field query). Doesn't matter how I situated the parentheses, the same thing happens.

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.

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.

回复收藏 0 原文

花桑 2024-11-21 15:07:33

MYsql

UPDATE tablename   SET columnName = UUID()

甲骨文

UPDATE tablename   SET columnName = SYS_GUID();

SQLSERVER

UPDATE tablename   SET columnName = NEWID();;

MYsql

UPDATE tablename   SET columnName = UUID()

oracle

UPDATE tablename   SET columnName = SYS_GUID();

SQLSERVER

UPDATE tablename   SET columnName = NEWID();;

回复收藏 0 原文

与风相奔跑 2024-11-21 15:07:33

看起来像是一个简单的错字。您不是说“...其中columnId 为 null”吗？

UPDATE db.tablename
  SET columnID = UUID()
  where columnID is null

Looks like a simple typo. Didn't you mean "...where columnId is null"?

UPDATE db.tablename
  SET columnID = UUID()
  where columnID is null

回复收藏 0 原文

泡沫很甜 2024-11-21 15:07:33

我遇到了几乎同样的问题。
我的情况下 uuid 存储为 BINARY(16) 并且具有 NOT NULL UNIQUE 约束。
当为每一行生成相同的 UUID 时，我遇到了问题，并且 UNIQUE 约束不允许这样做。所以这个查询不起作用：

UNHEX(REPLACE(uuid(), '-', ''))

但对我来说它有效，当我使用嵌套内部选择这样的查询时：

UNHEX(REPLACE((SELECT uuid()), '-', ''))

然后为每个条目生成唯一的结果。

回复收藏 0 原文

め可乐爱微笑 2024-11-21 15:07:33

UPDATE db.tablename SET columnID = (SELECT UUID()) where columnID is not null

UPDATE db.tablename SET columnID = (SELECT UUID()) where columnID is not null

回复收藏 0 原文

淡莣 2024-11-21 15:07:33

// 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()));

// 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()));

回复收藏 0 原文

潜移默化 2024-11-21 15:07:33

SELECT CONCAT(SUBSTRING(REPLACE(UUID(),'-',''), 1, 5), SUBSTRING(UPPER(REPLACE(UUID(),'-','')), 4, 5), SUBSTRING('@#$%(*&', FLOOR(RAND()*(1-8))+8, 1)) pass

我这样做了：SELECT五个小写字符，五个大写字符和一个特殊字符。

SELECT CONCAT(SUBSTRING(REPLACE(UUID(),'-',''), 1, 5), SUBSTRING(UPPER(REPLACE(UUID(),'-','')), 4, 5), SUBSTRING('@#$%(*&', FLOOR(RAND()*(1-8))+8, 1)) pass

I did this: SELECT five characters in lower case, five characters in upper case and one special character.

回复收藏 0 原文