DELIMITER $$
CREATE PROCEDURE generate_data2()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE j INT DEFAULT 0;
WHILE i < 10 DO
WHILE j <100 DO
INSERT INTO notifications (id,customer_id,tos,device_type,message,types,read_status,created_at,updated_at)
VALUES (
null,10000,'tos','device_type','message ','Types',1, DATE_FORMAT(DATE_SUB(now(),INTERVAL i DAY),"%Y-%m-%d %h:%m:%s"),
DATE_FORMAT(DATE_SUB(now(),INTERVAL 2 DAY),"%Y-%m-%d"));
SET j = j + 1;
END WHILE;
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
这个代码没有像我预期的那样工作。我想使用while循环每天生成100个数据。但每次循环一次仅持续一天。如果你执行我的代码，你就能理解这个问题。需要更好的专家解决方案。示例：对于2022-Jan-01 100条目2021年12月31日100条目2021年12月30日100条
您应该在每次I迭代中重置j
DELIMITER $$
CREATE PROCEDURE generate_data2()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE j INT DEFAULT 0;
WHILE i < 10 DO
SET J = 0;
#SELECT I,J;
WHILE j <100 DO
INSERT INTO notifications (id,customer_id,tos,device_type,message,types,read_status,created_at,updated_at)
VALUES (
null,10000,'tos','device_type','message ','Types',1, DATE_FORMAT(DATE_SUB(now(),INTERVAL i DAY),"%Y-%m-%d %h:%m:%s"),
DATE_FORMAT(DATE_SUB(now(),INTERVAL 2 DAY),"%Y-%m-%d"));
SET j = j + 1;
END WHILE;
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=85c31b4c54428d570057a31af38d1a03
我看到您标记了MariaDB。然后，您可以直接使用续集引擎对这样的查询执行此操作。
INSERT INTO通知(id、customer_id、tos、device_type、message、types、read_status、created_at、updated_at(
SELECT
NULL
,10000
,'tos','device_type','message '
,'Types'
,1
, NOW() - INTERVAL dayLoop.seq DAY
, NOW() - INTERVAL dayLoop.seq+1 DAY
FROM ( SELECT seq FROM seq_1_to_10 ) AS perDayLoop
CROSS JOIN ( SELECT seq FROM seq_1_to_10 ) AS dayLoop
ORDER BY dayLoop.seq, perDayLoop.seq
样本
MariaDB [test]> SELECT seq, NOW() - INTERVAL seq DAY FROM seq_1_to_5;
+-----+--------------------------+
| seq | NOW() - INTERVAL seq DAY |
+-----+--------------------------+
| 1 | 2022-01-07 09:48:09 |
| 2 | 2022-01-06 09:48:09 |
| 3 | 2022-01-05 09:48:09 |
| 4 | 2022-01-04 09:48:09 |
| 5 | 2022-01-03 09:48:09 |
+-----+--------------------------+
5 rows in set (0.001 sec)
MariaDB [test]>
sample2
MariaDB [test]> SELECT
-> NULL
-> ,10000
-> ,'tos','device_type','message '
-> ,'Types'
-> ,1
-> , NOW() - INTERVAL dayLoop.seq DAY
-> , NOW() - INTERVAL dayLoop.seq+1 DAY
-> , perDayLoop.seq
-> , dayLoop.seq
->
-> FROM ( SELECT seq FROM seq_1_to_10 ) AS perDayLoop
-> CROSS JOIN ( SELECT seq FROM seq_1_to_10 ) AS dayLoop
-> ORDER BY dayLoop.seq, perDayLoop.seq;
+------+-------+-----+-------------+----------+-------+---+------------------------------------+------------------------------------+-----+-----+
| NULL | 10000 | tos | device_type | message | Types | 1 | NOW() - INTERVAL dayLoop.seq DAY | NOW() - INTERVAL dayLoop.seq+1 DAY | seq | seq |
+------+-------+-----+-------------+----------+-------+---+------------------------------------+------------------------------------+-----+-----+
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 1 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 2 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 3 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 4 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 5 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 6 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 7 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 8 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 9 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-07 09:49:25 | 2022-01-06 09:49:25 | 10 | 1 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 1 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 2 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 3 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 4 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 5 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 6 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 7 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 8 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 9 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-06 09:49:25 | 2022-01-05 09:49:25 | 10 | 2 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 1 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 2 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 3 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 4 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 5 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 6 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 7 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 8 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 9 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-05 09:49:25 | 2022-01-04 09:49:25 | 10 | 3 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 1 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 2 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 3 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 4 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 5 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 6 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 7 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 8 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 9 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-04 09:49:25 | 2022-01-03 09:49:25 | 10 | 4 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 1 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 2 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 3 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 4 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 5 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 6 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 7 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 8 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 9 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-03 09:49:25 | 2022-01-02 09:49:25 | 10 | 5 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 1 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 2 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 3 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 4 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 5 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 6 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 7 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 8 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 9 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-02 09:49:25 | 2022-01-01 09:49:25 | 10 | 6 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 1 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 2 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 3 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 4 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 5 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 6 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 7 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 8 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 9 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2022-01-01 09:49:25 | 2021-12-31 09:49:25 | 10 | 7 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 1 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 2 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 3 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 4 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 5 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 6 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 7 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 8 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 9 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-31 09:49:25 | 2021-12-30 09:49:25 | 10 | 8 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 1 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 2 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 3 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 4 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 5 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 6 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 7 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 8 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 9 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-30 09:49:25 | 2021-12-29 09:49:25 | 10 | 9 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 1 | 10 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 2 | 10 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 3 | 10 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 4 | 10 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 5 | 10 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 6 | 10 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 7 | 10 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 8 | 10 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 9 | 10 |
| NULL | 10000 | tos | device_type | message | Types | 1 | 2021-12-29 09:49:25 | 2021-12-28 09:49:25 | 10 | 10 |
+------+-------+-----+-------------+----------+-------+---+------------------------------------+------------------------------------+-----+-----+
100 rows in set (0.001 sec)
MariaDB [test]>