os: centos 7.4.1708 db: mysql 8.0.20
版本
# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core)
#
#
# yum list installed |grep -i mysql80
mysql-community-client.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-common.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-devel.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-libs.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-libs-compat.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-server.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-test.x86_64 8.0.20-1.el7 @mysql80-community
mysql80-community-release.noarch el7-3 installed
# mysql -e "select version();"
+-----------+
| version() |
+-----------+
| 8.0.20 |
+-----------+
generate_seed
mysql> use test
mysql> DELIMITER $$
DROP PROCEDURE IF EXISTS `generate_seed`$$
CREATE PROCEDURE `generate_seed`(
IN f_begin_num INT UNSIGNED,
IN f_end_num INT UNSIGNED
)
BEGIN
DROP TABLE IF EXISTS generate_seed_tmp;
CREATE TEMPORARY TABLE generate_seed_tmp (id INT);
BEGIN
DECLARE i INT;
SET i = f_begin_num;
WHILE i <= f_end_num DO
INSERT INTO generate_seed_tmp VALUES (i);
SET i = i + 1;
END WHILE;
END;
END$$
DELIMITER ;
使用
mysql> call generate_seed(1,100);
mysql> select @a := @a + 1 as seq, date_sub(current_date(), interval @a day) from generate_seed_tmp,(select @a:=0) as seq;
+------+-------------------------------------------+
| seq | date_sub(current_date(), interval @a day) |
+------+-------------------------------------------+
| 1 | 2020-06-30 |
| 2 | 2020-06-29 |
| 3 | 2020-06-28 |
| 4 | 2020-06-27 |
| 5 | 2020-06-26 |
···
···
···
| 96 | 2020-03-27 |
| 97 | 2020-03-26 |
| 98 | 2020-03-25 |
| 99 | 2020-03-24 |
| 100 | 2020-03-23 |
+------+-------------------------------------------+
100 rows in set, 2 warnings (0.00 sec)
