mysql - 在MySQL中插入时检查并防止类似的字符串

简要信息
我有3张 table :
设置:

id
name

SetItem:

set_id
item_id
position

TempSet:

id

我有一个从Item表生成新的随机组合的函数。基本上，总是在成功生成之后，我才在Set表中创建一个新行，获取它的id并将所有项id添加到SetItem表中。
问题
每次生成新组合之前，我都会截断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中确实有30万行。

我有一个工作的例子。看来您无法像执行的那样使用标量子查询。我以这种方式工作:

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

关于mysql - 在MySQL中插入时检查并防止类似的字符串，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45057324/