mysql for insert_mysql循环插入数据

实验中经常会遇到需要多条数据的情况就想到了用SQL语句循环生成数据

DROP PROCEDURE if EXISTS test_insert;

DELIMITER ;;

CREATE PROCEDURE test_insert()

BEGIN

DECLARE y BIGINT DEFAULT 1;

WHILE y<100000

DO

INSERT INTO `sshshop`.`product` (`pname`, `market_price`, `shop_price`, `image`, `pdesc`, `is_hot`, `pdate`, `csid`) VALUES ('Thinkpad', '5999', '5099', 'products/dn2.jpg', 'Thinkpad', '1', '2018-05-09 14:23:07', '26');

SET y=y+1;

END WHILE ;

commit;

END;;

CALL test_insert();

运行这个需要点时间：

当然可以在插入的时候在名字属性上该些变动：

DROP PROCEDURE if EXISTS test_insert;

DELIMITER ;;

CREATE PROCEDURE test_insert()

BEGIN

DECLARE y BIGINT DEFAULT 1;

DECLARE n VARCHAR(255);

DECLARE b VARCHAR(255);

WHILE y<50000

DO

SET b="师傅送";

SET n=CONCAT("美了美",y,b,y);

INSERT INTO `sshshop`.`product` (`pname`, `market_price`, `shop_price`, `image`, `pdesc`, `is_hot`, `pdate`, `csid`) VALUES (n, '5999', '5099', 'products/dn2.jpg', 'Thinkpad', '1', '2018-05-09 14:23:07', '26');

SET y=y+1;

END WHILE ;

commit;

END;;

CALL test_insert();

这样就保证插入的数据不重复更具有测试性

还有就是双重循环插入数据：

DELIMITER ;;

CREATE PROCEDURE test_insert()

BEGIN

DECLARE a INT DEFAULT 1;

DECLARE b TINYINT DEFAULT 1;

WHILE (a <= 100) DO

-- repeat

SET a = a + 1;

-- select a;

WHILE (b <=20) DO

insert into school_sp_mj(school_id,mojor_id,status) values(a,b,1);

SET b = b + 1;

-- select b;

END WHILE;

SET b = 1;

-- select a;

-- until a >= i_PlayerCount

-- end repeat;

END WHILE;

commit;

END;;

CALL test_insert();