当用作函数参数时，MySQL UUID函数产生相同的值 [英] MySQL UUID function produces the same value when used as a function parameter
问题描述
UUID()函数每次被调用时都会产生一个不同的值，就像我期望的那样:
The UUID() function by itself produces a different value each time it is called, as I would expect it to do:
SELECT UUID() from INFORMATION_SCHEMA.TABLES LIMIT 3;
3bb7d468-f9c5-11e9-8349-d05099466715
3bb7d482-f9c5-11e9-8349-d05099466715
3bb7d492-f9c5-11e9-8349-d05099466715
但是，一旦我们在REPLACE()函数中使用它，它就会开始产生相同的值:
However, as soon as we use it within the REPLACE() function, it begins producing the same value:
SELECT REPLACE(UUID(),'-','-') from INFORMATION_SCHEMA.TABLES LIMIT 3;
e0f2d47a-f9c5-11e9-8349-d05099466715
e0f2d47a-f9c5-11e9-8349-d05099466715
e0f2d47a-f9c5-11e9-8349-d05099466715
此中断"此类从Select语句插入"语句，在该语句中，我们希望插入的每一行都具有唯一的值:
This 'breaks' Insert From Select statements like this where we expect each inserted row to have a unique value:
INSERT INTO MyTable (uid, tableName) -- uid is binary(16)
SELECT UNHEX(REPLACE(UUID(),'-','')), TABLE_NAME from INFORMATION_SCHEMA.TABLES;
注意，为方便起见，我使用的是信息模式的表列表.没关系，但是对于那些好奇的人，我们的PK是binary(16)形式的UUID.我不能改变这一点.请不要专注于此.
UUID()函数是不确定性的，而REPLACE()函数是确定性的.我本来希望UUID()函数的不确定性特性导致REPLACE()函数表现出好像每一行都有不同的自变量，但是似乎DB引擎通过假设UUID()保持不变.
The UUID() function is non-deterministic, while the REPLACE() function is deterministic. I would have expected the non-deterministic characteristic of the UUID() function to result in the REPLACE() function behaving as if it had a different argument for each row, but it seems as though the DB engine is over optimizing by assuming the UUID() to be constant.
我还用另一个不确定性函数RAND()测试了此行为，在这种情况下，REPLACE()函数按预期工作！
I also tested this behavior with another non-deterministic function, RAND(), and in this case the REPLACE() function worked as we'd expect!
SELECT REPLACE(RAND(),' ',' ') from INFORMATION_SCHEMA.TABLES LIMIT 3;
0.911571646026868
0.626416072832808
0.6977608461843439
问题:
是否可以执行从选择插入"并在选择中每行以二进制16格式生成唯一的UUID?
Is there a way to perform an "Insert From Select" and generate a unique UUID in binary 16 form per row in the select?
为什么会这样?这是一个错误吗?
Why is this happening? Is this a bug?
更新
我在本地使用5.7.27:
I am using 5.7.27 locally:
mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64)
但这最终将部署到AWS RDS实例.大声笑... Terraform(脚本部署)启动了引擎版本为5.7.16的AWS RDS实例.
But this will end up deploying to an AWS RDS instance. lol... The Terraform (scripted deploy) spins up an AWS RDS instance with engine version 5.7.16.
在AWS控制台中，我看到对版本5.7.26(在5.7静脉中)和8.0.16(在8.0静脉中)的支持.我将讨论升级已部署的引擎版本.我很想按照@Schwern的建议将PK列定义更改为默认值.
Looking in the AWS console, I see support up to version 5.7.26 (in the 5.7 vein) and 8.0.16 (in the 8.0 vein). I'll discuss upgrading the deployed engine version. I'd love to change the PK column definitions to default the values as @Schwern has suggested.
变通
直到我可以征得其他人同意版本更改为止，我正在通过使用临时表作为生成的id值的中间存储来进行升级.
Until I can get others to agree to a version change, I'm moving forward by using a temporary table as intermediate storage for generated id values.
CREATE TEMPORARY TABLE GeneratedIds (
generatedId varchar(36) NOT NULL,
tableName text NOT NULL
);
INSERT INTO GeneratedIds (generatedId, tableName)
SELECT UUID(), TABLE_NAME from INFORMATION_SCHEMA.TABLES;
INSERT INTO MyTable (uid, tableName) -- uid is binary(16)
SELECT UNHEX(REPLACE(generatedId,'-','')), tableName FROM GeneratedIds;
DROP TABLE GeneratedIds;
这不是很优雅，但是确实有效.就我而言，我正在一个sql迁移文件中工作，在其中可以以一种内聚的方式将这种sql序列串在一起.我不建议在代码中执行此操作；闻起来.
This is not very elegant, but it does work. In my case I am working within a sql migration file where I can string together this kind of sequence of sql in a cohesive manner. I wouldn't recommend doing this in code; it smells.
结论
这确实是MySQL中的错误.我快速搜索了他们的错误数据库，但没有找到它.无论如何，上面的SQL语句都说明了此缺陷，@ Schwern和我已经表明，此错误已在5.7.27版(准确)和8.0.16版(可能是8. .)中修复.仅测试了8.0.16和8.0.18).
This does appear to be a bug in MySQL. I did a quick search of their bug DB but I did not find a mention of it. Regardless, the SQL statements above illustrate the defect, and @Schwern and I have shown that this bug has been fixed in version 5.7.27 (exactly) and version 8.0.16 (possibly all 8.., only tested 8.0.16 and 8.0.18).
版本8.0.16测试:
Version 8.0.16 test:
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT REPLACE(UUID(),'-','-') from INFORMATION_SCHEMA.TABLES LIMIT 3;
+--------------------------------------+
| REPLACE(UUID(),'-','-') |
+--------------------------------------+
| 96f9205a-fdc6-11e9-87de-d05099466715 |
| 96f920f9-fdc6-11e9-87de-d05099466715 |
| 96f9213e-fdc6-11e9-87de-d05099466715 |
+--------------------------------------+
3 rows in set (0.00 sec)
推荐答案
MySQL 5.6.46, 5.7.28, nor 8.0.18 do not appear to have this issue. Upgrade if you can.
升级的好处之一是您现在可以将函数用作列的默认值.这使您可以将UUID设置为主键的默认值，从而避免了此问题以及许多其他问题.您也可以使用 uuid_to_bin 和 bin_to_uuid .
One of the benefits of upgrading is you can now use functions as column defaults. This allows you to set the UUID as the default for your primary key sidestepping this and many other problems. You can also use uuid_to_bin and bin_to_uuid.
create table MyTable (
uid binary(16) primary key default(uuid_to_bin(uuid())),
tableName text not null
);
INSERT INTO MyTable (tableName)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES;
如果您无法升级，则可以使用触发器设置此答案中的默认主键.
If you can't upgrade, you can use a trigger to set the default primary key as in this answer.
这篇关于当用作函数参数时，MySQL UUID函数产生相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
