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.

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.

Answer 2

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.

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