MySQL 快速检查哈希是否存在。

Question

我试图创建一个MySQL函数，它接受n和m作为输入，并从查询结果中生成m个ids的n个随机唯一组合。

每次调用该函数将返回一个组合，该组合必须与所有先前的组合不同。

在生成过程中，必须检查另一个表：如果组合已经存在，请继续循环，直到每个组合都唯一。以破折号分隔的ids形式返回组合，如果没有足够的空间返回唯一组合，则返回false。

因此，我会像这样获取100个随机项：

SELECT
    `Item`.`id`
FROM
    `Item`
LEFT JOIN `ItemKeyword` ON `Item`.`id` = `ItemKeyword`.`ItemID`
WHERE
    (`Item`.`user_id` = '2')
AND(`ItemKeyword`.`keywordID` = 7130)
AND(`Item`.`type` = 1)
ORDER BY RAND()
LIMIT 100

以-连接的itemID的md5被用来存储过去的组合。因此，我需要将这个查询的结果通过-连接在一起，并创建它的md5值。然后，发送另一个查询到名为Combination的第二个表格，并通过hash列检查它是否存在。并继续这个循环，直到我得到了n个结果。我无法想出如何正确快速地实现这个功能。有什么建议吗？

更新：

整个SQL转储在这里：https://gist.github.com/anonymous/e5eb3bf1a10f9d762cc20a8146acf866。

- demonoid

---

你真的需要哈希吗？对我来说，这似乎是不必要的开销。只需在“Combination”表中的两个列上使用唯一索引即可。 - fancyPants

@fancyPants 现在想象一下，将至少有5位数字的100个数字连接起来，并在它们之间加上100个破折号。你不觉得MD5是更好的解决方案吗？ - demonoid

Answer 1

如果您通过md5测试唯一性，需要在进行md5之前对列表进行排序。可以通过SELECT MD5('1-2'), MD5('2-1');演示此操作。

删除LEFT，它似乎是无用的。之后，优化器可以选择从ItemKeyword而不是Item开始。（如果不了解数据分布情况，我无法确定这是否有帮助。）

（如果您提供每个表的SHOW CREATE TABLE将会很有帮助。在没有它们的情况下，我假设您正在使用InnoDB并且具有PRIMARY KEY(id)和PRIMARY KEY(keywordID)。）

需要“组合”索引：

Item: INDEX(user_id, type, id)
ItemKeyword: INDEX(ItemID, keywordID)

ItemKeyword 看起来像是一个多对多映射表。大多数这样的表可以得到改进，首先应该删除id。详见7 tips on many:many。

我在你的二次处理中有些迷茫。

My tips on RAND 可能会有所帮助。

架构评审

• PRIMARY KEY 是 UNIQUE KEY 是 INDEX；消除冗余索引。
• INT(4) -- (4) 没有实际含义；INT 总是32位（4字节）并具有很大的范围。请参见 SMALLINT UNSIGNED （2字节，0.. 64K 范围）。
• MD5 应声明为 CHAR(32) CHARACTER SET ascii，而不是 255，不是 utf8。（latin1 是可以的。）
• 表 Combination（id + hash）似乎是无用的。相反，在表 Item 中将 KEY md5 (md5) USING BTREE, 更改为 UNIQUE(md5) 即可。
• 你已经开始使用 SET NAMES utf8mb4;，但是表格（及其列）仍然是 utf8。表情符号和中文需要utf8mb4；大多数其他文本则不需要。

解决这些问题后，原始问题可能得到解决（并进行一些清理）。如果现在不能，请添加进一步的澄清。

缩小版

1. 获取一个排序后的 m 个唯一 ID 列表。（我需要“排序”用于下一步，而且由于您正在寻找“组合”，因此似乎不需要“排列”。）

SELECT GROUP_CONCAT(id) AS list
    FROM (
        SELECT id FROM tbl
            ORDER BY RAND()
            LIMIT $m
         ) AS x;

2. 检查唯一性。通过对上面的MD5(list)进行检查，在“已使用”的MD5表中查找。注意：除非您要在少量id列表中请求大量组合，否则重复项不太可能出现（尽管不是不可能）。

3. 传递list。但是，它是由逗号分隔的id字符串。最好在应用程序代码中拆分它，而不是在MySQL函数中进行。

4. 您将如何处理该列表？这可能很重要，因为将步骤4与步骤3合并可能很方便。

底线：我只会在SQL中执行步骤1和步骤2的一部分；我会在应用程序代码中构建一个“函数”来完成其余部分。

- Rick James

---

如何在进行MD5之前进行排序？我更新了查询，请看一下，我做错了什么？ - demonoid

以下是转储内容：https://gist.github.com/anonymous/e5eb3bf1a10f9d762cc20a8146acf866 - demonoid

我添加了更多。 - Rick James

Rick，这是问题的压缩版。实际上，“Combination”表中还有很多其他数据。你提到的所有内容都将被修复，并且对于此问题不是主要问题。对我来说最重要的是创建一个函数，它会循环直到在查询响应中找到n个由m个ID组成的组合。请尽量帮助解决主要问题。感谢您的时间。我很感激！ - demonoid

做了另一次尝试。 - Rick James

Answer 2

排列组合

DROP FUNCTION IF EXISTS unique_perm;

DELIMITER //
CREATE FUNCTION unique_perm()
    RETURNS VARCHAR(255) CHARACTER SET ascii
    NOT DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
SET @n := 0;
iterat: LOOP
    SELECT SUBSTRING_INDEX(
             GROUP_CONCAT(province ORDER BY RAND() SEPARATOR '-'),
             '-', 3) INTO @list   -- Assuming you want M=3 items
        FROM world.provinces;
    SET @md5 := MD5(@list);
    INSERT IGNORE INTO md5s (md5) VALUES (@md5);  -- To prevent dups
    IF ROW_COUNT() > 0 THEN  -- Check for dup
        RETURN @list;       -- Got a unique permutation
    END IF;
    SET @n := @n + 1;
    IF @n > 20 THEN
        RETURN NULL;    -- Probably ran out of combinations
    END IF;
END LOOP iterat;
END;
//
DELIMITER ;

输出：

mysql> SELECT unique_perm(),  unique_perm(),  unique_perm()\G
*************************** 1. row ***************************
unique_perm(): New Brunswick-Nova Scotia-Quebec
unique_perm(): Alberta-Northwest Territories-New Brunswick
unique_perm(): Manitoba-Quebec-Prince Edward Island
1 row in set (0.01 sec)

备注：

• 我硬编码了 M=3；根据需要进行调整。（它可以作为参数传递。）
• 更改列和表名以满足您的需求。
• 如果没有对 @n 进行测试，当你用尽组合时，你可能会进入循环。（但是，如果 N 足够大，那是“不可能”的，所以你可以删除测试。）
• 如果 M 足够大，你将需要增加 @@group_concat_max_len。还有，RETURNS。
• 需要创建表 md5s ( md5 CHAR(32) CHARACTER SET ascii PRIMARY KEY ) ENGINE=InnoDB。并且，在调用此函数的批次之间，你需要 TRUNCATE md5s。
• 这是一个有效的示例。
• 缺陷：它提供唯一的排列，而不是唯一的组合。如果这不足够，请继续阅读...

组合

DROP FUNCTION IF EXISTS unique_comb;

DELIMITER //
CREATE FUNCTION unique_comb()
    RETURNS VARCHAR(255) CHARACTER SET ascii
    NOT DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
SET @n := 0;
iterat: LOOP
    SELECT GROUP_CONCAT(province ORDER BY province SEPARATOR '-') INTO @list
        FROM ( SELECT province FROM world.provinces
                  ORDER BY RAND() LIMIT 2 ) AS x;   -- Assuming you want M=2 items
    SET @md5 := MD5(@list);
    INSERT IGNORE INTO md5s (md5) VALUES (@md5);  -- To prevent dups
    IF ROW_COUNT() > 0 THEN  -- Check for dup
        RETURN @list;       -- Got a unique permutation
    END IF;
    SET @n := @n + 1;
    IF @n > 20 THEN
        RETURN NULL;    -- Probably ran out of combinations
    END IF;
END LOOP iterat;
END;
//
DELIMITER ;

输出：

mysql> SELECT unique_comb(),  unique_comb(),  unique_comb()\G
*************************** 1. row ***************************
unique_comb(): Quebec-Yukon
unique_comb(): Ontario-Yukon
unique_comb(): New Brunswick-Nova Scotia
1 row in set (0.01 sec)

注：

• 子查询会增加一些成本。
• 请注意，每个输出字符串中的项目现在（必须）有序。

- Rick James

---

我不太明白第一个函数和第二个函数结果的区别。 - demonoid

所以我需要将所有现有的组合复制到MD5表中吗？ - demonoid

第一个将提供魁北克-育空和育空-魁北克，而第二个则不会。（请参阅“组合”和“排列”的定义。）md5s表最初为空，并随着您调用函数而逐渐建立——每次调用增加M行。 - Rick James

我已经有大约10,000个哈希值了。我需要将它们复制到新的md5s表中，还是只需用“SELECT count(*) FROM Combination WHERE Combination.hash = @md5;”替换“#INSERT IGNORE INTO md5s (md5) VALUES (@md5);”？ - demonoid

尝试使用INSERT，然后检查Row_count比使用SELECT更快。 - Rick James