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.

Beggary

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. – Kickapoo

Are you sure? I tried a similar test -- I got distinct UUIDs. – Guatemala

in the 2020s! never ever ever use MySql's crap UUIDs. use only normal UUID.v4() – Tithonus

Answer 1

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.

Beggary

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. – Hammers

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. – Hammers

saved me hours! Thank you :D – Baber

Answer 2

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

Downstate

Why? Care to explain? – Mirella

Answer 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().

Leavetaking

Answer 4

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.

Ozzy

Well, V1 has just as bad performance unless you rearrange the bits. – Guatemala

Inserting V4 UUID's is pretty easy, even without backend support for that: #61110721 – Langer

Answer 5

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

Frohman