MySql 插入选择 uuid()

发布于 11-14 12:39 字数 933 浏览 6 评论 0原文

假设您有一个表:

`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 插入到所有新创建的行中。

因此,如果您从以下内容开始:

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?

我知道以下内容在 MSSQL 中按预期工作:

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

nb 我知道我可以选择结果,循环遍历它们,并为我的 PHP 代码中的每一行发出单独的 INSERT 命令,但我不想这样做。我希望工作在应该完成的数据库服务器上完成。

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

半夏半凉2024-11-21 12:39:23

结果 uuid() 每行生成一个不同的 uuid。

但是,MySql 似乎只随机生成第二个块,而不是像我通常期望的那样随机生成所有块。估计是为了效率更高。

因此乍一看,uuid 看起来是相同的,而实际上 MySql 已经更改了第二个块。例如,

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

我假设如果发生碰撞,它会重试。

我的不好。

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.

誰ツ都不明白2024-11-21 12:39:23

请尝试使用 MID(UUID(),1,36) 而不是 uuid()。

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

や三分注定2024-11-21 12:39:23

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

MySQL 不允许将表达式作为默认值。您可以通过允许该字段为空来解决此问题。然后添加插入/更新触发器,当为空时,将该字段设置为 uuid()。

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

半衾梦2024-11-21 12:39:23

MySQL 的 UUID() 函数生成 V1 UUID,分为时间、序列和节点字段。如果在单个节点上调用,则只有时间字段中的几个位会不同;这被称为时间唯一性。如果同时在不同节点上调用,节点字段会不同;这被称为空间独特性。两者的结合非常强大,可以保证通用唯一性,但它也会泄露有关每个 V1 UUID 创建时间和地点的信息,这可能是一个安全问题。哎呀。

V4 UUID 现在通常更流行,因为它们将数据(以及更多数据)散列在一起,因此不会泄漏任何内容,但您需要不同的函数来获取它们 - 如果您有,请注意它们会对性能产生什么影响高插入量; MySQL(至少目前)不太擅长索引(伪)随机值,这就是为什么他们为您提供 V1。

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.

迷爱2024-11-21 12:39:23

首先使用php uniqid()函数生成uniq字符串
并插入到 ID 字段。

First generate an uniq string using the php uniqid() function
and insert to the ID field.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文