MySql 插入选择 uuid()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6300849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql Insert Select uuid()
提问by Richard
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.
MySql 会将相同的 uuid 插入到所有新创建的行中。
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?
如何命令 MySql 为每一行创建不同的 uuid？
I know that the following works as expected in MSSQL:
我知道以下在 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.
nb 我知道我可以选择结果，遍历它们并为我的 PHP 代码中的每一行发出单独的 INSERT 命令，但我不想这样做。我希望工作在应该完成的数据库服务器上完成。
回答by Richard
Turns out uuid() isgenerating a different uuid per row.
圈出UUID（）是生成每行不同的UUID。
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 似乎只随机生成第二个块。估计效率会更高。
So at a glance the uuids appear identical when in fact MySql has altered the 2nd chunk. e.g.
因此，当实际上 MySql 更改了第二个块时，乍一看 uuid 看起来是相同的。例如
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.
我的错。
回答by Denis de Bernardy
How do I command MySql to create a different uuid foreach row?
如何命令 MySql 为每一行创建不同的 uuid？
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 triggerswhich, when null, set the field to uuid().
MySQL 不允许将表达式作为默认值。您可以通过允许该字段为空来解决此问题。然后添加插入/更新触发器，当为空时，将字段设置为 uuid()。
回答by Lam Nguy?n
Please try with MID(UUID(),1,36)instead of uuid().
请尝试使用MID(UUID(),1,36)而不是 uuid()。
回答by StephenS
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.
MySQL 的 UUID() 函数生成 V1 UUID，分为时间、序列和节点字段。如果在单个节点上调用它，时间字段中只有几位会有所不同；这称为时间唯一性。如果你同时在不同的节点上调用它，节点字段会有所不同；这被称为空间唯一性。将两者结合起来非常强大，可以保证通用唯一性，但它也会泄露有关每个 V1 UUID 何时何地创建的信息，这可能是一个安全问题。哎呀。
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.
V4 UUID 现在通常更受欢迎，因为它们将这些数据（以及更多）散列在一起，因此不会泄漏任何内容，但是您需要一个不同的函数来获取它们——如果您有，请注意它们对性能的影响高插入量；MySQL（至少现在）在索引（伪）随机值方面不是很擅长，这就是为什么他们给你的是 V1。
回答by HYDER ALI
First generate an uniq string using the php uniqid() function and insert to the ID field.
首先使用php uniqid()函数生成一个uniq字符串 并插入到ID字段中。
