mysql - 这段消除排列的SQL代码如何改进？

Question

问题陈述如下:

-一个表有一个INIT结构

(number1 INT not null, number2 INT not null, ..., number7 INT not null)

-我想将表 INIT 的所有行插入到表“选项卡”中，但我不想在“选项卡”中有 2 行，这样一个是另一个的排列。所以，例如，如果 (1,2,3,7,19,21,6) 和 (19,2,3,7,1,21,6) 是 INIT 中的行，则只有一个其中必须以“制表符”结束。它们中的哪一个最终出现在“制表符”中并不重要。

-我下面的代码所做的是:我保留了一个辅助表“aux”与INIT结构相同。我遍历表 INIT 的所有行和每一行在 INIT 中，我按组件的递增顺序对其进行排序，因此如果 (1,2,3,7,19,21,6) 是INIT 中的一行，我对其进行排序(1、2、3、6、7、19、21)并检查它是否在“辅助”中。如果是我继续下一行。否则，我在“制表符”中插入 (1,2,3,7,19,21,6)。

我在包含 300,000 行的表 INIT 上运行了这个过程，我估计运行需要 7 个多小时。我想知道如何改进该程序的运行时间。

DECLARE done BOOLEAN default 0;
DECLARE n1,n2,n3,n4,n5,n6,n7 INT;
DECLARE o1,o2,o3,o4,o5,o6,o7 INT;
DECLARE my_cursor cursor  FOR select * from INIT;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;       
OPEN my_cursor;

drop table if exists aux;
create table aux(
  number1 INT not null,
  number2 INT not null,
  number3 INT not null,
  number4 INT not null,
  number5 INT not null,
  number6 INT not null,
  number7 INT not null,
 );
 create table temp( number INT );

REPEAT
   truncate table temp;

   FETCH my_cursor INTO n1,n2,n3,n4,n5,n6,n7;
        INSERT INTO temp values(n1);
        INSERT INTO temp values(n2);    
        INSERT INTO temp values(n3);
        INSERT INTO temp values(n4);
        INSERT INTO temp values(n5);
        INSERT INTO temp values(n6);
        INSERT INTO temp values(n7);
        BEGIN
           DECLARE done2 BOOLEAN default 0;
           DECLARE my_cursor2 cursor  FOR select * from temp order by number;  
           OPEN my_cursor2;
             FETCH my_cursor2 INTO o1;
             FETCH my_cursor2 INTO o2;
             FETCH my_cursor2 INTO o3;
             FETCH my_cursor2 INTO o4;
             FETCH my_cursor2 INTO o5;
             FETCH my_cursor2 INTO o6;
             FETCH my_cursor2 INTO o7;

             IF NOT EXISTS (SELECT * FROM aux where number1=o1 AND number2=o2 AND number3=o3 
                            AND number4=o4 AND number5 = o5 AND number6 = o6 AND number7=o7 ) 
             THEN
                 INSERT INTO tab VALUES (n1,n2,n3,n4,n5,n6,n7);
             END IF;
           CLOSE my_cursor2;
         END;
UNTIL done END REPEAT;
CLOSE my_cursor;

编辑:- 在INIT的每一行中，所有的整数都是不同的。-INIT的主键是(number1,number2,...,number7)

Answer 1

您正在对每一行进行繁重的查询……这不是一个好方法。

相反，您可以使用一些数据库功夫来完成工作而无需存储过程:

insert into tab
select number1, number2, number3, number4, number5, number6, number7 from (
  select number1, number2, number3, number4, number5, number6, number7, 
    group_concat(number order by number) as sig from (
      select number1, number2, number3, number4, number5, number6, number7, number1 as number
      union all select number1, number2, number3, number4, number5, number6, number7, number2
      union all select number1, number2, number3, number4, number5, number6, number7, number3
      union all select number1, number2, number3, number4, number5, number6, number7, number4
      union all select number1, number2, number3, number4, number5, number6, number7, number5
      union all select number1, number2, number3, number4, number5, number6, number7, number6
      union all select number1, number2, number3, number4, number5, number6, number7, number7) a
) group by sig) b

这里涉及的关键技巧是:

• 内部选择允许 group_concat 按照标准顺序对数字进行分组，以便比较组合
• group_concat with order by 给你一个唯一的数字签名
• 在 mysql 中使用 group by without aggregation 为每个 group-by 列值提供第一行

顺便说一句，正确的术语是组合而不是排列

此外，我还没有对此进行测试，因此可能存在错位的括号等，但它应该“基本上”有效