MySQL中数字辅助表的建立

 

数字辅助表是一个只包含从1到N的N个整数的简单表，N通常非常大；
如何创建这样一个辅助表：
（1）我们可以通过下面这个方式创建：

mysql> CREATE TABLE nums(
    -> a INT UNSIGNED NOT NULL PRIMARY KEY
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)

 

CREATE PROCEDURE pCreateNums(n INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
TRUNCATE TABLE NUMS;
WHILE s <= n DO
BEGIN 
INSERT INTO nums SELECT s;
SET s = s+1;
END;
END WHILE;
END;

测试一下，插入100000行的数据：

CALL pCreateNums(100000);

用时：

[SQL]CALL pCreateNums(100000);
受影响的行: 1
时间: 249.552s

（2）我们还可以通过下面这个方式创建：

CREATE PROCEDURE pCreateNums(n INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
TRUNCATE TABLE nums;
INSERT INTO nums SELECT s;
WHILE s*2 <= n DO
BEGIN
INSERT INTO nums SELECT a+s FROM nums;
SET s = s*2;
END;
END WHILE;
END;

测试一下，插入200000行的数据：

CALL pCreateNums(200000);

用时：

mysql> CALL pCreateNums(200000);
Query OK, 65536 rows affected (1.60 sec)

（3）第一种方式没有任何的问题，除了效率，因为要插入100000行的数据，需要的时间相对较长；第二种方式是最先插入｛1｝，然后是｛2｝，｛3，4｝，｛5，6，7，8｝……是按照2的指数次进行插入的，实际只执行了10多次插入操作；第二种方式的缺点是，数字辅助表是按照2的指数次进行插入的，因此上述实际的插入行数是131072，而不是200000行：

mysql> SELECT COUNT(1) FROM nums;
+----------+
| COUNT(1) |
+----------+
|   131072 |
+----------+
1 row in set (0.11 sec)