MySql插入选择uuid() [英] MySql Insert Select uuid()

问题描述

假设您有一张桌子:

`item`

带有字段:

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

并且:

Unique(`id`)

然后您致电:

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

MySql会将相同的uuid插入所有新创建的行中.

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

您最终会得到:

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

如何命令MySql为每一行创建一个不同的uuid?

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

我知道以下功能可以在MSSQL中正常工作:

I know that the following works as expected in MSSQL:

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

n.b.我知道我可以选择结果，遍历它们，然后为我的PHP代码中的每一行发出一个单独的INSERT命令，但是我不想这样做.我希望这项工作在应该完成的数据库服务器上完成.

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.

推荐答案

显示uuid()是每行生成一个不同的uuid.

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

但MySql似乎不是随机生成所有块，而是像我通常期望的那样随机生成第二个块.大概会更有效率.

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.

因此，实际上MySql更改了第二个块时，uuid看起来相同.例如

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.

我不好.

这篇关于MySql插入选择uuid()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！