2.MySQL存储过程

By youfang at: 2022-08-13

Mysql 存储过程

第一版

1
2
3
4
5
6
7
8
9
10
11
12
CREATE DEFINER=`root`@`localhost` PROCEDURE `product_insert`()
BEGIN
DECLARE Y BIGINT DEFAULT 1;
WHILE Y<100000
DO
INSERT INTO product(product_title,product_name,remarks,merchant_id,create_time,update_time,classify_1,classify_2,classify_3,brand_id,user_id,user_ip,freight,pic_url,sales_amount,product_amount,product_status)
VALUES(CONCAT('商品_iphone8_title_test_',Y),CONCAT('商品_iphone8_name_test_',Y),CONCAT('商品_iphone8_remrk_test_',Y),Y,NOW(),NOW(),1,1,1,1,1000,'192.168.18.100',5000,'http://www.baidu.com',5000,5000,0);
SET Y=Y+1;
COMMIT;
END WHILE ;

END

第二版

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE PROCEDURE insert_product(IN start_key int, IN total int)
BEGIN
DECLARE end_key BIGINT;
set end_key = start_key * total;
WHILE start_key<end_key
DO
INSERT INTO product(product_title,product_name,remarks,merchant_id,create_time,update_time,classify_1,classify_2,classify_3,brand_id,user_id,user_ip,freight,pic_url,sales_amount,product_amount,product_status)
VALUES(CONCAT('商品_iphone8_title_test_',start_key),CONCAT('商品_iphone8_name_test_',start_key),CONCAT('商品_iphone8_remrk_test_',start_key),start_key,NOW(),NOW(),1,1,1,1,1000,'192.168.18.100',5000,'http://www.baidu.com',5000,5000,0);
SET start_key=start_key+1;
COMMIT;
END WHILE ;

END;

-- call insert_product(100000, 100000)

第三版

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE PROCEDURE insert_product3(IN total BIGINT)
BEGIN
DECLARE start_key BIGINT DEFAULT 0;
DECLARE max_id BIGINT DEFAULT 0;

SELECT COUNT(1) into max_id from product;

WHILE start_key<total
DO
SET max_id=max_id+1;
INSERT INTO product(product_title,product_name,remarks,merchant_id,create_time,update_time,classify_1,classify_2,classify_3,brand_id,user_id,user_ip,freight,pic_url,sales_amount,product_amount,product_status)
VALUES(CONCAT('商品_iphone8_title_test_',max_id),CONCAT('商品_iphone8_name_test_',max_id),CONCAT('商品_iphone8_remrk_test_',max_id),max_id,NOW(),NOW(),1,1,1,1,1000,'192.168.18.100',5000,'http://www.baidu.com',5000,5000,0);
SET start_key=start_key+1;
COMMIT;
END WHILE ;

END;


-- call insert_product3(10)