MySQL为多行生成UUID（） [英] MySQL generate UUID() for multiple rows

问题描述

我正在尝试将UUID添加到某些MySQL数据库（MySQL 5.7.9）中的某些表中。首先，我首先添加一列以接收UUID：

I'm trying to add UUIDs to some tables in some of my MySQL databases (MySQL 5.7.9). First of all, I start by adding a column to receive the UUIDs:

ALTER TABLE `mytable` ADD COLUMN `Uuid` BINARY(16) DEFAULT NULL;

然后，对于表中已经存在的所有条目，我都会生成UUID（因为数据库的UUID字段将收到NULL值。

Then, for all entries that are already in the table, I generate UUIDs (since every entry in the DB will have received a NULL value in it's UUID field.

UPDATE `mytable` SET Uuid= unhex(replace(uuid(),'-','')) WHERE Uuid IS NULL;

现在，事情是我这样做会产生一些非常奇怪的行为；在我的某些数据库上，生成的每个UUID都是唯一的（如预期的那样）。但是，在其他数据库上，所有生成的UUID都是相同的（不相似，相同）。

Now, the thing is that I get some very weird behaviour by doing this; on some of my databases, every UUID generated is unique (as expected). However, on other databases, all generated UUIDs are identical (not similar, identical).

我怀疑这是由于MySQL查询优化器所致，因为不同数据库实例之间的行为不一致（全部在MySQL 5.7.9上），但是，我不知道如何解决此问题。

I suspect that this is due to the MySQL query optimiser, since behaviour is not consistent between different database instances (all on MySQL 5.7.9). However, I have no clue how to solve this issue.

那么，MySQL专家，我做错什么了吗？

So, MySQL gurus, am I doing something wrong?

推荐答案

我也遇到了同样的问题，但是解决方案是后来的答案他联系了问题。我的默认字符集为utf8mb4，理论是在执行sql之前，它将uuid（）的utf8结果隐式转换为常量字符串。将其他参数转换为utf8可避免以下情况：

I had the same problem, but the solution was a later answer to the linked question. I had a default character set of utf8mb4, and the theory is that it implicitly converts uuid()'s utf8 result to a constant string, before the sql executes. Converting the other parameters to utf8 avoids that:

UPDATE `mytable` SET Uuid= unhex(replace(uuid(), _utf8'-', _utf8'')) WHERE Uuid IS NULL;

后面的答案链接到相关（不是）错误报告。 此SO问题看起来很相关。

The later answer links to the relevant (not a) bug report. This SO issue looks related.

这篇关于MySQL为多行生成UUID（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！