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.