mysql通过时分秒添加数据，每秒添加数据，生成测试数据

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

一、前言

		mysql通过时分秒添加数据，每秒添加数据，生成测试数据

---

二、要求：

• 目前需要每间隔一秒的数据测试
  

创建这样的历史数据，每秒的数据都要包含

---

三、表创建：

略

---

四、sql编写：

4.1 每分钟数据

drop procedure if exists addRoomPrice;
create procedure addRoomPrice()
begin
  declare i int default 0;
  set i = 0;
--   start transaction;
    while i < 5000000 do
    INSERT INTO `ry-cloud`.`test` (`id`, `table_code`, `energy`, `time`) VALUES 
			(NULL, 'A00001', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00002', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00003', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00004', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00005', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00006', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00007', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00008', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00009', '200', date_sub('2021-04-26 23:59:59', interval -i second));
    set i = i + 1;
    end while;
  commit;
end

call addRoomPrice();

4.2 每小时数据

drop procedure if exists addRoomPrice;
create procedure addRoomPrice()
begin
  declare i int default 0;
  set i = 0;
--   start transaction;
    while i < 5000000 do
    INSERT INTO `ry-cloud`.`test` (`id`, `table_code`, `energy`, `time`) VALUES 
			(NULL, 'A00001', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00002', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00003', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00004', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00005', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00006', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00007', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00008', '200', date_sub('2021-04-26 23:59:59', interval -i second)),
			(NULL, 'A00009', '200', date_sub('2021-04-26 23:59:59', interval -i second));
    set i = i + 1;
    end while;
  commit;
end

call addRoomPrice();

4.2 每天数据

drop procedure if exists addRoomPrice;
create procedure addRoomPrice()
begin
  declare i int default 0;
  set i = 0;
--   start transaction;
    while i < 730 do 
    INSERT INTO `ry-cloud`.`dev_energy_days` (`id`,`energy_name`,`energy_code`, `energy`, `difference_energy`, `statistics_time`,`create_time`) VALUES 
   (NULL,'南京银行', 'A00001', '200','150', date_sub('2020-04-26', interval -i day),NOW()),
   (NULL,'南京银行', 'A00002', '200','150', date_sub('2020-04-26', interval -i day),NOW()),
   (NULL,'南京银行', 'A00003', '200','150', date_sub('2020-04-26', interval -i day),NOW());
    set i = i + 1;
    end while; 
  commit;
end

call addRoomPrice();