Mysql插入..。关于用新键重新插入重复键

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()函数，并且它依赖于MySQL rand()，我可以解释为什么您的代码失败了。

根据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

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

更新

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

此函数返回使用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

除非您有大量的行计数，否则这个函数应该足够安全地使用而不关心冲突。

测试

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

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

每个函数在4 000万行之后发生碰撞的总和是：

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

两种场景中的数字冲突都会随着行数的增加而增加。