Generate GUID in MySQL for existing Data?

Question

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

Ie

are you really sure,they are same?I have tried ,most the characters are same,but there are a few differences in the generated uuid – Silvia

Yes, I confirm, it's the same! – Gratianna

It works for me - the differences are minor, but are there. Quickest way to check is to add a UNIQUE constraint to the column. – Galop

Sorry to necro an old post here, but SET columnID = UUID() works - it's just if you're doing it over a large number of rows, the majority of the UUID characters will appear the same but there will be subtle differences. +1 to PSU's answer – Footage

Answer 1

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.

Serrano

yeah, it should work even in 5.0. But don't forget to drop the trigger! – Serrano

yeah sure :) just wondering whether I need to check for duplicates after or whether this will create unique values for every row in the column ? – Ie

If UUID is implemented properly (and I believe it is), you should be able to create unique index without checking for duplicates. – Serrano

I updated my answer with another approach which may also be useful. – Serrano

your original code would work, just change columnId=UUID() to columnId=(SELECT UUID()). Worked great for me. all the generated values are very close to being the same but each is unique. – Clop

UPDATE db.tablename SET columnID = UUID() works for me – Krasner

Non trigger solution worked for me in laravel migrations where the original answer did not. I think it might have to do with how the transactions are executed. – Thanhthank

Just a note that @Serrano might want to add to the answer: in 5.6 (I haven't tested others), if the columnId=(SELECT UUID()) is within a stored procedure, all the UUID values will be identical. – Jodee

Is this trigger work properly on multi user envoirement? – Melanous

Answer 2

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

Kuvasz

At first I thought this had inserted duplicate UUIDs because they all begin and end the same, but they are in fact slightly different. – Kienan

@SamBarnum because UUID is generated based on the machine and timestamp. As a query that takes milliseconds to run, they have to be very very close indeed... but never the same... a good thing to assure you, is to add an UNIQUE index to that column. – Ferriage

The accepted answer seems an overkill comparing to this! – Malcom

At least in mariadb (10.1.26) this does not seem to work, giving the same uuid for every record. – Crocodile

This generated the same UUID on every record for me, presumably because it's in a subquery and MySQL will execute the inner query first and use the same value for all rows. To solve it, remove the subquery: UPDATE sri_issued_quiz SET quiz_id=uuid(); – Gainful

@ChrisWhite, your pending edit should also revise the "inner select" part of the answer, or else it doesn't flow with your proposed code. – Lomond

Answer 3

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)

Caruncle

In my case, i needed hyphens in the generated GUID. I used this: SELECT INSERT(INSERT(INSERT(INSERT(MD5(UUID()), 9, 0, '-'), 14, 0, '-'), 19, 0, '-'), 24, 0, '-') Query is not very pretty but it does the job. – Presumable

Isn't md5 less unique than UUIDs? I'd worry about collisions. – Cardoza

@Cardoza you are right. Even though a collision is rare, MD5 is not unique. I would not recommend using this method for the result the OP wants. Good discussions about it here: link – Ignoble

Answer 4

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

Lavettelavigne

perfect perfect perfect!! such small thing can have huge impact!! – Berwickupontweed

Can confirm, simplest and only solution that works for me to set different UUID for many rows, – Hasdrubal

Answer 5

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.

Expertize

Thanks, indeed you saved me some time. :) – Tiffany

Answer 6

MYsql

UPDATE tablename   SET columnName = UUID()

oracle

UPDATE tablename   SET columnName = SYS_GUID();

SQLSERVER

UPDATE tablename   SET columnName = NEWID();;

Mccann

Answer 7

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

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

Escapement

I had the same thought when I read the question, but I don't think so: sounds like his columns contain values, but not UNIQUE values. The answers given long before your answer already show what is needed. There should not be a WHERE clause. And the values generated are very similar, so must look at them closely to see that they are indeed different. – Desantis

Answer 8

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.

Schulman

Answer 9

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

Durware

Please add some explanation on how your code works. Code without comments is not always easy to understand to other SO users – Faludi

if u want to update uuid in existing data, run query as above with your condition. – Durware

Answer 10

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

Suannesuarez

Answer 11

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.

Cumings

Answer 12

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.

Integrator