MySql Insert Select uuid()

Question

Say you have a table:

`item`

With fields:

`id` VARCHAR( 36 ) NOT NULL
,`order` BIGINT UNSIGNED NOT NULL

And:

Unique(`id`)

And you call:

INSERT INTO `item` (
`item`.`id`,`item`.`order`
) SELECT uuid(), `item`.`order`+1

MySql will insert the same uuid into all of the newly created rows.

So if you start with:

aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa, 0
bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb, 1

You'll end up with:

aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa, 0
bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb, 1
cccccccc-cccc-cccc-cccc-cccccccccccc, 1
cccccccc-cccc-cccc-cccc-cccccccccccc, 2

How do I command MySql to create a different uuid for each row?

I know that the following works as expected in MSSQL:

INSERT INTO item (
id,[order]
) SELECT newid(), [order]+1

n.b. I know I could SELECT the results, loop through them and issue a separate INSERT command for each row from my PHP code but I don't want to do that. I want the work to be done on the database server where it's supposed to be done.

Are you sure it's returning the exact same value for every row? I would expect most of the characters to be the same in each value when you call UUID() like that, so scanning them visually they might look the same, but each one will still be unique if you look closely. — Ike Walker, Jun 10 '11 at 01:35
Are you sure? I tried a similar test -- I got distinct UUIDs. — Rick James, Sep 30 '18 at 04:34
in the 2020s! **never ever ever use MySql's crap UUIDs. use only normal UUID.v4()** — Fattie, Jun 12 '22 at 21:16

score 27 · Answer 1 · edited Dec 31 '18 at 04:02

27

Turns out uuid() is generating a different uuid per row.

But instead of generating all the chunks randomly, as I would normally expect, MySql appears to only be generating the 2nd chunk randomly. Presumably to be more efficient.

So at a glance the uuids appear identical when in fact MySql has altered the 2nd chunk. e.g.

cccccccc-cccc-cccc-cccc-cccccccccccc
ccccdddd-cccc-cccc-cccc-cccccccccccc
cccceeee-cccc-cccc-cccc-cccccccccccc
ccccffff-cccc-cccc-cccc-cccccccccccc

I assume if there is a collision it would try again.

My bad.

edited Dec 31 '18 at 04:02

Alexis Wilke

19,179
10
84
156

answered Jun 10 '11 at 12:01

Richard

271
2
2

7

This is because of the specification of UUIDs that MySQL is using. A large part of the string is based on the MAC address of the server, which presumably in your setup won't change from one INSERT to the next. The rest of the string is time based. UUIDs are not random. – Tom Wright Jul 12 '11 at 17:46
4

Also, the chances of there ever being a collision are effectively zero (because of the time element inherent in this mode of UUID generation), but if there ever were a collision it would not be picked up because you are not using keys on your fields. – Tom Wright Jul 12 '11 at 17:48
saved me hours! Thank you :D – Björn Jul 13 '20 at 11:49

score 3 · Answer 2 · answered Jan 05 '17 at 05:07

3

Please try with MID(UUID(),1,36) instead of uuid().

answered Jan 05 '17 at 05:07

Lâm Nguyễn

39
2

score 3 · Answer 3 · answered Jul 31 '18 at 23:24

MySQL's UUID() function generates V1 UUIDs, which are split into time, sequence and node fields. If you call it on a single node, only a few bits in the time field will be different; this is referred to as temporal uniqueness. If you call it on different nodes at the exact same time, the node fields will be different; this is referred to as spatial uniqueness. Combining the two is very powerful and gives a guarantee of universal uniqueness, but it also leaks information about the when and where each V1 UUID was created, which can be a security issue. Oops.

V4 UUIDs are generally more popular now because they hash that data (and more) together and thus don't leak anything, but you'll need a different function to get them--and beware what they'll do to performance if you have high INSERT volume; MySQL (at least for now) isn't very good at indexing (pseudo)random values, which is why V1 is what they give you.

Well, V1 has just as bad performance _unless_ you [_rearrange the bits_](http://mysql.rjweb.org/doc.php/uuid). — Rick James, Sep 30 '18 at 04:27
Inserting V4 UUID's is pretty easy, even without backend support for that: https://stackoverflow.com/questions/61110221/mysql-expression-for-random-uuid4 — J_H, Apr 11 '20 at 21:48

score 3 · Answer 4 · edited May 23 '17 at 12:02

3

How do I command MySql to create a different uuid foreach row?

MySQL won't allow expressions as a default value. You can work around this by allowing the field to be null. Then add insert/update triggers which, when null, set the field to uuid().

edited May 23 '17 at 12:02

Community

1
1

answered Jun 10 '11 at 09:11

Denis de Bernardy

75,850
13
131
154

score -4 · Answer 5 · answered Mar 11 '13 at 10:54

-4

First generate an uniq string using the php uniqid() function and insert to the ID field.

answered Mar 11 '13 at 10:54

HYDER ALI

86
1
1
11

MySql Insert Select uuid()

5 Answers5

Linked