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 ;
This code not working as I expected. I want to generate each day 100 data using while loop. But each time it is looping one times only for 1 day. If you execute my code you can understand that issue. Need expert solution in better way. Example: For 2022-Jan-01 100 entry For 2022-Dec-31 100 entry For 2022-Dec-30 100 entry
CodePudding user response：
You should reset j inside every I iteration
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
CodePudding user response：
I have seen that you tag MariaDB. Then you can directly use the sequel Engine to do that with a Query like this.
INSERT INTO notifications (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
samples
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]>