Mysql插入..ON DUPLICATE KEY使用新密钥重新插入

Question

我有一个类似的查询

INSERT INTO data(data_uuid, ...) VALUES(uuid_v4(), ...); 

我已经阅读了关于重复更新的内容，但我想要的不是更新现有列，而是用更新的uuid重新插入行。有没有什么结构可以让这里的生活变得更加美好？

我一直遇到这样一个错误：

ERROR 1062 (23000): Duplicate entry '0f7018da-4df5-4e22-b995-3b3c3e4e85d3' for key 'data_uuid'

为了暂时解决这个问题，我填充了UUID，但我希望有一个更干净的解决方案，仍然使用UUID。

uuid函数如下所示：

CREATE FUNCTION uuid_v4() RETURNS char(36) CHARSET latin1
BEGIN
SET @h1 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
SET @h2 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
SET @h3 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
SET @h6 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
SET @h7 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
SET @h8 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
SET @h4 = CONCAT('4', LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'));
SET @h5 = CONCAT(HEX(FLOOR(RAND() * 4 + 8)),
LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'));
RETURN LOWER(CONCAT(
@h1, @h2, '-', @h3, '-', @h4, '-', @h5, '-', @h6, @h7, @h8
));
END 

Answer 1

正如评论中所说，如果您与只有一百万行的UUID发生冲突(是的，只是(，那是因为您的随机UUID生成使用了一些弱随机生成器。

由于您现在发布了uuid_v4()函数，并且它依赖于MySQLrand()，我可以解释您的代码失败的原因。

根据mysql文档：http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html

RAND((并不意味着是一个完美的随机生成器。这是一条很快的路根据需要生成可在相同MySQL版本的平台。

这意味着您不能使用mysql来生成uuid，至少不能使用RAND()函数。

您将需要在mysql之外生成uuid。在许多语言中有几个库：

• PHP：https://github.com/ramsey/uuid
• Java：https://www.baeldung.com/java-uuid
• Javascript：https://www.npmjs.com/package/uuid

始终检查您选择的库是否使用加密安全的随机生成器。

更新

可以使用random_bytes()函数在MySQL端生成安全的UUID V4：

此函数返回使用SSL库的随机数生成器生成的len随机字节的二进制字符串。

因此我们可以将您的功能更新为：

CREATE FUNCTION uuid_v4s()
RETURNS CHAR(36)
BEGIN
-- 1th and 2nd block are made of 6 random bytes
SET @h1 = HEX(RANDOM_BYTES(4));
SET @h2 = HEX(RANDOM_BYTES(2));
-- 3th block will start with a 4 indicating the version, remaining is random
SET @h3 = SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3);
-- 4th block first nibble can only be 8, 9 A or B, remaining is random
SET @h4 = CONCAT(HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64)+8),
SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3));
-- 5th block is made of 6 random bytes
SET @h5 = HEX(RANDOM_BYTES(6));
-- Build the complete UUID
RETURN LOWER(CONCAT(
@h1, '-', @h2, '-4', @h3, '-', @h4, '-', @h5
));
END

这个函数应该足够安全，可以在不考虑碰撞的情况下使用，除非你有一个非常大的行数。

测试

我创建了以下测试场景：插入随机UUIDv4作为表的主键，直到创建40.000.000行。当发现冲突时，将更新该行，递增collisions列：

INSERT INTO test (uuid) VALUES (uuid_v4()) ON DUPLICATE KEY UPDATE collisions=collisions+1;

每个函数在4000万行后的冲突总数为：

+----------+----------------+
| RAND()   | RANDOM_BYTES() |
+----------+----------------+
|       55 |              0 |
+----------+----------------+

这两种情况下的冲突数量往往会随着行数的增长而增加。