mysql生成1-N连续表

mysql生成1-N连续表

###准备

新建表nums：

mysql> desc nums;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | bigint(20) | NO   | PRI | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.03 sec)

方案一

普通做法:

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_num`(IN `total` int)
BEGIN
	DECLARE s INT DEFAULT 1;
	WHILE s < total DO
		BEGIN
			INSERT INTO nums SELECT s;
			SET s=s+1;
		END;
	END WHILE;
END

调用存储过程:

call generate_num(1000)

Procedure executed successfully
时间: 185.239s

看起来效率并不高…

方案二

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_num_new`(IN `total` int)
BEGIN
	DECLARE s INT DEFAULT 1;
	INSERT INTO nums SELECT s;
	WHILE s*2 <= total DO
		BEGIN
			INSERT INTO nums SELECT id+s FROM nums;
			set s=s*2;
		END;
END WHILE;
END

分析一下，先插入{1},再插入{2},接着{3,4},接着{6,7,8,9}…,乍一看，似乎有规律：是以2的n次幂生成的

调用存储过程：

call generate_num_new(200000);

Procedure executed successfully
时间: 25.795s

效率杠杠的。。。，不过

mysql> select count(*) from nums;
+----------+
| count(*) |
+----------+
|   131072 |
+----------+
1 row in set (0.28 sec)

不过好像没有200000条，因为这是以2的n次幂生成的，如果真的要有200000条，就要求出2^n>200000的最小数

扩展

我们可以利用数字辅助表生成连续时间:

CREATE DEFINER=`root`@`localhost` PROCEDURE `createDateTimes`(IN `start` date,IN `end` date)
BEGIN
	#Routine body goes here...
	SELECT DATE_ADD(start,INTERVAL id-1 day) FROM nums WHERE id <=DATEDIFF(end,start)+1;
END

调用存储过程:

mysql> call createDateTimes('2019-03-12','2019-03-22');
+-----------------------------------+
| DATE_ADD(start,INTERVAL id-1 day) |
+-----------------------------------+
| 2019-03-12                        |
| 2019-03-13                        |
| 2019-03-14                        |
| 2019-03-15                        |
| 2019-03-16                        |
| 2019-03-17                        |
| 2019-03-18                        |
| 2019-03-19                        |
| 2019-03-20                        |
| 2019-03-21                        |
| 2019-03-22                        |
+-----------------------------------+
11 rows in set (0.04 sec)

Query OK, 0 rows affected (0.00 sec)