简要信息

我有3张桌子：

Set:

id
name

SetItem:

set_id
item_id
position

TempSet:

id

我有一个函数可以生成新的随机组合Item桌子。基本上，总是在成功生成之后，我在中创建一个新行Set表，获取它的 id 并将所有项目 id 添加到SetItem table.

Problem

每次在生成新组合之前我都会截断TempSet表，将新的项目 ID 填入该表，并通过与之前的组合进行比较来检查相似性百分比SetItem桌子。如果新的组合相似度大于或等于30%，我需要阻止这个组合并重新生成新的组合。

相似性意味着 - 先前生成的组合上存在元素。所以，想法是：

如果新生成的集合中超过 3 个元素在某个先前生成的集合上重复，则阻止它并尝试生成另一个组合。

这是生成新组合的函数：

  CREATE DEFINER = `root` @`localhost` FUNCTION `gen_uniq_perm_by_kw` (
    comboSize INT ( 5 ),
    tries INT ( 3 ) 
    ) RETURNS text CHARSET utf8 SQL SECURITY INVOKER BEGIN
    iterat :
    LOOP
        DELETE 
        FROM
            `TempSet`;
        INSERT INTO `TempSet` ( `id` ) (
            SELECT
                `i`.`id` 
            FROM
                `Item` AS `i`
            ORDER BY
                RAND( ) 
                LIMIT comboSize 
            );
        IF
            (
            SELECT
                1 
            FROM
                `SetItem` 
            GROUP BY
                `set_id` 
            HAVING
                sum(
                CASE
                        
                        WHEN EXISTS (
                        SELECT
                            id 
                        FROM
                            `TempSet` 
                        WHERE
                            `id` = `item_id` 
                            LIMIT 1 
                            ) THEN
                            1 ELSE 0 
                        END 
                        ) / count( 1 ) * 100 >= 30 
                        LIMIT 1 
                        ) < 1 THEN
                        RETURN ( SELECT GROUP_CONCAT( id SEPARATOR '-' ) FROM `TempSet` );
                    
                END IF;
                
                SET tries := tries - 1;
                IF
                    tries = 0 THEN
                        RETURN NULL;
                    
                END IF;
                
            END LOOP iterat;
        
END

当我测试它时，即使新生成的组合的元素不存在于任何其他先前生成的组合中，它也会返回 null 作为结果。

我的问题是，我做错了什么？

我的问题是，我做错了什么？

您的 SetItem 表中没有任何数据。

编辑：您评论说这是错误的； SetItem 中有 300k 行。

我有一个工作的例子。看来您不能像您所做的那样使用标量子查询。我是这样工作的：

DROP FUNCTION IF EXISTS gen_uniq_perm_by_kw;
DELIMITER ;;
CREATE DEFINER = `root` @`localhost` FUNCTION `gen_uniq_perm_by_kw` (comboSize INT, tries INT) RETURNS text CHARSET utf8 SQL SECURITY INVOKER
BEGIN
        iterat :
        LOOP
                DELETE FROM `TempSet`;

                INSERT INTO `TempSet` (`id`)
                SELECT `i`.`id` FROM `Item` AS `i` ORDER BY RAND() LIMIT comboSize;

                IF EXISTS(
                        SELECT set_id,
                                SUM(CASE WHEN EXISTS (SELECT id FROM `TempSet` WHERE `id` = `item_id` LIMIT 1) THEN 1 ELSE 0 END) AS group_sum,
                                COUNT(*) AS group_count
                        FROM `SetItem`
                        GROUP BY `set_id`
                        HAVING group_sum * 10 / group_count < 3
                ) THEN
                        RETURN (SELECT GROUP_CONCAT(id SEPARATOR '-') FROM `TempSet`);
                END IF;

                SET tries = tries - 1;

                IF tries = 0 THEN
                        RETURN NULL;
                END IF;
        END LOOP iterat;
END

我还以更简单的方式使其工作，无需使用 SUM 和额外的子查询：

DROP FUNCTION IF EXISTS gen_uniq_perm_by_kw;
DELIMITER ;;
CREATE DEFINER = `root` @`localhost` FUNCTION `gen_uniq_perm_by_kw` (comboSize INT, tries INT) RETURNS text CHARSET utf8 SQL SECURITY INVOKER
BEGIN
        iterat :
        LOOP
                DELETE FROM `TempSet`;

                INSERT INTO `TempSet` (`id`)
                SELECT `i`.`id` FROM `Item` AS `i` ORDER BY RAND() LIMIT comboSize;

                IF EXISTS(
                        SELECT s.set_id,
                                COUNT(t.id) AS group_matches,
                                COUNT(*) AS group_count
                        FROM SetItem AS s LEFT OUTER JOIN TempSet AS t ON t.id = s.item_id
                        GROUP BY s.set_id
                        HAVING group_matches * 10 / group_count < 3
                ) THEN
                        RETURN (SELECT GROUP_CONCAT(id SEPARATOR '-') FROM `TempSet`);
                END IF;

                SET tries = tries - 1;

                IF tries = 0 THEN
                        RETURN NULL;
                END IF;
        END LOOP iterat;
END