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 inserts the same uuid in all newly created rows.

So, if you start with:

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

As a result, you will receive:

 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 configure MySql to create another uuid foreach string?

I know that in MSSQL it works as expected:

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

nb I know that I could select the results, skip them and issue a separate INSERT command for each line from my PHP code, but I don't want to do this. I want the work to be done on the database server where it was supposed to be done.

Answer 1

Turns uuid () off, creating a different uuid for each line.

But instead of generating all the pieces randomly, as I usually expected, MySql apparently generates the 2nd piece randomly. Presumably to be more effective.

So, at first glance, uuids seem to be the same when infact MySql changed the second piece. eg.

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

I assume that if there is a collision, he will try again.

My bad.

Answer 2

How do I configure MySql to create another uuid foreach string?

MySQL will not allow expressions as the default value. You can get around this by allowing this field to be null. Then add insert / update triggers , which, when null, set this field to uuid ().

Answer 3

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

Answer 4

First create an uniq string using the php uniqid () function and paste the ID into the field.