时间戳

首页 > 解决方案 > Mysql INSERT ... ON DUPLICATE KEY Reinsert with new key

mysql - Mysql INSERT ... ON DUPLICATE KEY Reinsert with new key

问题描述

我有这样的查询

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

标签： mysqlsql

解决方案

正如评论中所说，如果您与 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。有许多语言的一些库：

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

更新

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

除非您的行数非常大，否则此功能应该足够安全，无需担心碰撞。

测试

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

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

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

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

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

推荐阅读