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

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 — xiaoyifang, Mar 22 '17 at 08:56
It works for me - the differences are minor, but are there. Quickest way to check is to add a UNIQUE constraint to the column. — PSU, Jun 19 '20 at 11:57
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 — joelc, Feb 19 '21 at 17:08

score 157 · Answer 1 · edited Jul 09 '12 at 15:18

157

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

edited Jul 09 '12 at 15:18

Tisho

8,320
6
44
52

answered Jul 09 '12 at 14:55

Rakesh Prajapati

1,579
2
9
2

48

At first I thought this had inserted duplicate UUIDs because they all begin and end the same, but they are in fact slightly different. – Sam Barnum Oct 22 '13 at 17:04
7

@SamBarnum because `UUID` is generated based on the [machine and timestamp](http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid). 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. – balexandre Nov 06 '14 at 09:07
5

The accepted answer seems an overkill comparing to this! – Aritz Apr 06 '17 at 07:53
8

At least in mariadb (10.1.26) this does not seem to work, giving the same uuid for every record. – johanvdw Oct 01 '18 at 08:20
8

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();` – Chris White Nov 08 '18 at 16:00
@ChrisWhite, your pending edit should also revise the "inner select" part of the answer, or else it doesn't flow with your proposed code. – Joshua Huber Nov 08 '18 at 17:50

a1ex07 · Accepted Answer · 2011-06-09T21:32:13.027

105

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.

edited Jun 09 '11 at 21:32

answered Jun 08 '11 at 15:13

a1ex07

36,826
12
90
103

yeah, it should work even in 5.0. But don't forget to drop the trigger! – a1ex07 Jun 08 '11 at 15:24
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 ? – Tom Jun 08 '11 at 15:29
If `UUID` is implemented properly (and I believe it is), you should be able to create unique index without checking for duplicates. – a1ex07 Jun 08 '11 at 15:32
I updated my answer with another approach which may also be useful. – a1ex07 Jun 08 '11 at 15:56
2

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. – EJay Sep 28 '12 at 20:20
UPDATE db.tablename SET columnID = UUID() works for me – Marku Aug 10 '14 at 21:09
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. – Matt Bucci Oct 12 '16 at 02:15
1

Just a note that @a1ex07 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. – Phil Apr 10 '18 at 18:28
Is this trigger work properly on multi user envoirement? – Rizwan Saleem Jun 12 '18 at 07:40

score 27 · Answer 3 · answered Dec 23 '14 at 15:13

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)

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. — solo, Jul 26 '16 at 09:05
Isn't md5 less unique than UUIDs? I'd worry about collisions. — Adam, Jun 01 '18 at 16:18
@Adam 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](https://stackoverflow.com/questions/2444321/how-are-hash-functions-like-md5-unique) — Miky Dal, Feb 23 '22 at 15:49

score 23 · Answer 4 · edited Aug 22 '12 at 17:32

23

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

edited Aug 22 '12 at 17:32

LittleBobbyTables - Au Revoir

32,008
25
109
114

answered Aug 22 '12 at 17:18

Brad Johnson

231
2
2

2

perfect perfect perfect!! such small thing can have huge impact!! – thekosmix Aug 17 '17 at 06:44
Can confirm, simplest and only solution that works for me to set different UUID for many rows, – rabudde Nov 12 '20 at 08:24

score 8 · Answer 5 · edited May 23 '17 at 12:02

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.

Thanks, indeed you saved me some time. :) – Klaus Nov 13 '21 at 16:52 — Klaus, Nov 13 '21 at 16:52

score 4 · Answer 6 · edited Dec 08 '14 at 14:29

4

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

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

edited Dec 08 '14 at 14:29

dwaz

644
1
8
21

answered Mar 15 '14 at 05:31

evan.leonard

989
2
8
10

1

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. – ToolmakerSteve Apr 01 '15 at 17:33

score 4 · Answer 7 · answered Feb 23 '18 at 07:50

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.

score 3 · Answer 8 · edited Sep 24 '20 at 14:08

3

MYsql

UPDATE tablename   SET columnName = UUID()

oracle

UPDATE tablename   SET columnName = SYS_GUID();

SQLSERVER

UPDATE tablename   SET columnName = NEWID();;

edited Sep 24 '20 at 14:08

Leandro Bardelli

10,561
15
79
116

answered Jan 19 '20 at 05:24

Hugo R

2,613
1
14
6

score 2 · Answer 9 · answered Jan 25 '20 at 08:35

2

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

answered Jan 25 '20 at 08:35

Ashutosh Niranjan

89
7

2

Please add some explanation on how your code works. Code without comments is not always easy to understand to other SO users – Simas Joneliunas Jan 25 '20 at 09:52
if u want to update uuid in existing data, run query as above with your condition. – Ashutosh Niranjan Feb 01 '20 at 05:58

Leonardo Filipe · Answer 10 · 2022-08-23T05:01:05.303

1

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

edited Aug 23 '22 at 05:01

answered Mar 06 '20 at 21:26

Leonardo Filipe

1,534
15
9

score 1 · Answer 11 · edited May 24 '23 at 22:51

1

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.

edited May 24 '23 at 22:51

Kirby

15,127
10
89
104

answered Aug 22 '22 at 15:26

Rodolfo Luna

829
9
19

score 0 · Answer 12 · answered May 08 '21 at 08:59

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.

Generate GUID in MySQL for existing Data?

12 Answers12

Linked