mysql数字辅助表_关于数字的经典SQL编程问题:数字辅助表

【IT168 技术】数字辅助表是一个只包含从1到N的N个整数的简单表，N通常很大。因为数字辅助表是一个非常强大的工具，可能经常需要在解决方案中用到它，笔者建议创建一个持久的数字辅助表，并根据需要填充一定数据量的值。

实际上如何填充数字辅助表无关紧要，因为只需要运行这个过程一次。不过还可以对填充语句进行优化。一般的SQL编程人员会想到用如下方法来生成1～N的数。

CREATE TABLE Nums(

aINTUNSIGNEDNOTNULLPRIMARY KEY

)ENGINE=InnoDB;

CREATE PROCEDURE pCreateNums (cntINTUNSIGNED)

BEGIN

DECLARE sINTUNSIGNED DEFAULT1;

TRUNCATE TABLE Nums;WHILEs<=cntDOBEGIN

INSERT INTO NumsSELECTs;SETs=s+1;END;ENDWHILE;END;

这个方法没有任何的问题，只是效率不高。例如要插入100 000行的数据，在笔者的四核苹果电脑上至少需要1分钟。

mysql>CALLpCreateNums (100000);

Query OK,1row affected (1min11.56sec)

这个方法的开销主要在于INSERT语句被执行了100 000次。我们可以通过下面这个方法来创建数字辅助表。

CREATE PROCEDURE pFastCreateNums (cntINTUNSIGNED)

BEGIN

DECLARE sINTUNSIGNED DEFAULT1;

TRUNCATE TABLE Nums;

INSERT INTO NumsSELECTs;WHILEs*2<=cntDOBEGIN

INSERT INTO NumsSELECTa+s FROM Nums;SETs=s*2;END;ENDWHILE;END;

在这个存储过程中，变量s保存插入该表的行数。该过程先把1插入数字辅助表，然后当s*2<=cnt成立时执行循环。在每次迭代中，该过程把数字辅助表当前所有行的值加上s后再插入数字辅助表中，即先插入{1}，然后是{2}，{3,4}，{5，6，7，8}，{9,10,11,12,13,14,15,16}，以此类推。因此这个存储过程的执行时间非常之快。要插入200 000行数据，情况如下：

mysql>CALLpFastCreateNums (200000);

Query OK,65536rows affected (1.00sec)

可以看到执行时间缩短到了1秒钟，性能提高了70多倍。究其原因，是因为实际执行INSERT的次数少了。这里我们是按照2的指数次进行插入的，实际只执行了17次插入操作。这个解决方案的唯一缺点是，数字辅助表是按照2的指数次进行插入的，因此上述实际的插入行数是131 072，而不是200 000行。查询一下刚才插入的数据，结果如下：

mysql>SELECTCOUNT(1) FROM Nums;+----------+| count(1) |+----------+|131072|+----------+1row inset(0.03sec)

不过这不是一个很大的问题，因为我们可以在取出数据时使用<=来截取指定的行数，如：

SELECT*FROM Nums WHERE a<=100000

有了这张辅助表，用户可以通过它来辅助很多其他应用。例如，在数据仓库中，通常需要生成某个时间范围内的时间维度表，这时使用数字辅助表会非常简单和快捷，示例如下：

CREATE PROCEDURE pCreateDimTime(startDATE,endDATE)

BEGINSELECTDATE_ADD(start,INTERVAL a-1DAY)

FROM Nums WHERE a<=DATEDIFF(end,start)+1;END;

作者简介

姜承尧(DavidJiang)，《MySQL技术内幕：SQL编程》、《MySQL技术内幕：InnoDB存储引擎》作者，资深MySQL数据库专家，MySQL开源分支版本InnoSQL的创始人，独立数据库咨询顾问。不仅擅长于数据库的管理和维护，还擅长于数据库的开发，同时一直致力于MySQL数据库底层实现原理的研究和探索，对高性能数据库和数据仓库也有深刻而独到的理解。目前就职于网易研究院，担任后台技术中心技术经理一职，从事MySQL数据库底层以及云的相关的开发工作。