在MySQL中为现有数据生成GUID吗? [英] Generate GUID in MySQL for existing Data?

问题描述

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

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中做到这一点?

How do I do this in MySQL ?

我尝试过

UPDATE db.tablename
  SET columnID = UUID()
  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
//

然后执行

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

和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

再次

更新: 看来您的原始查询也应该起作用(也许您不需要WHERE columnID is not null，因此不需要我所有花哨的代码.

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.

这篇关于在MySQL中为现有数据生成GUID吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！