快速生成数字辅助表

        数字辅助表只有一个整数列，包含从1到N个整数序列值，N通常很大。对MySQL来讲，数字辅助表是一个强大的工具，编写SQL语句时经常用数据表与数字辅助表做笛卡尔积来创建额外的行。建议创建一个持久的数据辅助表，并根据需要填充一定数据量的值。        实际上如何填充数字辅助表无关紧要，因为只需要运行这个过程一次，不过还是可以对此过程进行优化。假设需要为如下数字辅助表生成数据：

create table nums (a bigint unsigned not null primary key) engine=innodb;

方案一：平凡低效

drop procedure if exists pcreatenums;
delimiter //
create procedure pcreatenums(cnt bigint)
begin
    declare s int default 1;
    set session autocommit=0;
    while s<=cnt do
        insert into nums values(s);
        set s=s+1;
    end while;
    commit;
end;
//

        这个存储过程没很简单，就是一个循环，每次插入一条数据，以生成的数据行数作为循环次数。在我的环境中执行这个过程生成1000000行需要执行接近1分24秒。效率不高的原因在于insert语句被执行了1000000次。
 

mysql> call pcreatenums(1000000);
Query OK, 0 rows affected (1 min 24.39 sec)

方法二：高效迭代

drop procedure if exists pcreatenums;
delimiter //
create procedure pcreatenums(cnt int)
begin
    declare s int default 1;
    set session autocommit=0;
    insert into nums select s;
    while s<=cnt do
        insert into nums select a+s from nums where a+s <=cnt;
        set s=s*2;
    end while;
    commit;
end;
//

        这次执行只用了不到不到17秒。

mysql> call pcreatenums(1000000);
Query OK, 0 rows affected (16.53 sec)

        在这个存储过程中，变量 s 保存插入nums表的行数。循环开始前先插入 1 条数据，然后当 s 小于等于所要生成的数据行数时执行循环。在每次迭代中，该过程把nums表当前所有行的值加上 s 后再插nums表中。这样每次循环插入的行数以2的幂次方递增，insert语句只被执行了21次，其中还包括作为种子数据的第一次插入。因此这个过程的执行速度很快。

方法三：一次生成

set session cte_max_recursion_depth=1000000;
insert into nums 
with recursive temp (n) as (select 1 union all select n+1 from temp where n < 1000000) select n from temp;

        这种方法利用MySQL 8 提供的CTE（Common Table Expressions）功能，用递归一次性生成所有数据，只需要不到13秒，性能进一步提高了四分之一。

mysql> insert into nums 
    -> with recursive temp (n) as (select 1 union all select n+1 from temp where n < 1000000) select n from temp;
Query OK, 1000000 rows affected (12.28 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

        CTE可以实现类似Oracle中connect by的递归功能，但功能更强大，能够解决非常复杂的查询问题。https://dev.mysql.com/doc/refman/8.0/en/with.html是MySQL官方文档对CTE的说明。