Generate GUID in MySQL for existing Data?


Mysql Problem Overview

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

Mysql Solutions

Solution 1 - Mysql

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 :-)

Solution 2 - Mysql

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
  SET new.guid_column := (SELECT UUID());

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.

Solution 3 - Mysql

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)

Solution 4 - Mysql

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

Solution 5 - Mysql

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.

Solution 6 - Mysql

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

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

Solution 7 - Mysql

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:

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

But for me it worked, when i used such a query with nested inner select:

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

Then is produced unique result for every entry.

Solution 8 - Mysql


UPDATE tablename   SET columnName = UUID()


UPDATE tablename   SET columnName = SYS_GUID();


UPDATE tablename   SET columnName = NEWID();;

Solution 9 - Mysql

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

Solution 10 - Mysql

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

Solution 11 - Mysql

I got this error when using mysql as sql_mode = "". After some testing, I decided that the problem was caused by this usage. When I tested on the default settings, I found that this problem was not there. Note: Don't forget to refresh your connection after changing the mode.


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionTomView Question on Stackoverflow
Solution 1 - MysqlRakesh PrajapatiView Answer on Stackoverflow
Solution 2 - Mysqla1ex07View Answer on Stackoverflow
Solution 3 - MysqlImran-UKView Answer on Stackoverflow
Solution 4 - MysqlBrad JohnsonView Answer on Stackoverflow
Solution 5 - MysqlenobrevView Answer on Stackoverflow
Solution 6 - Mysqlevan.leonardView Answer on Stackoverflow
Solution 7 - MysqlOleksandr KorniienkoView Answer on Stackoverflow
Solution 8 - MysqlHugo RView Answer on Stackoverflow
Solution 9 - MysqlAshutosh NiranjanView Answer on Stackoverflow
Solution 10 - MysqlLeonardo FilipeView Answer on Stackoverflow
Solution 11 - MysqlDeniz AktürkView Answer on Stackoverflow