mysql数字辅助表_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)