mysql存储过程实现同时多表写入，构造创建商品数据

布满荆棘的人生 2023-09-25 13:42 42阅读 0赞

**前言**  
通常在做性能测试的过程中，我们需要构造一下性能测试数据，有些可以通过调用API，直接构造数据，但是可能会存在一些场景，需要我们直接在数据库中插入数据，通常我们对于一些较为见到的场景，可以直接循环插入一张表中，但是如果我们遇到一些场景，比如创建商品，可能会设计到同时插入多张表，那么我们如何实现呢？  
**数据库表设计**  
下面我们以电商的项目为例，通过数据库实现插入商品数据，那么在创建商品数据之前，我们来了解一下表结构，![在这里插入图片描述][1e8154a27a8b4112b18def05da3f636e.png]  
![在这里插入图片描述][8a20d861fe6a47449381da34c75000cd.png]  
![在这里插入图片描述][f95b6933bfe24c3c9cad0bba9d695166.png]  
我们可以看到三张表，分别是商品表、产品表和库存表，我们在创建商品的时候，需要分别在这三张表中插入数据。那么我们可以看到es\_product产品表中需要依赖es\_good表中的goods\_id；  
而库存表es\_product\_store表中依赖goods\_id 和 productid。

那么mysql如果合适每次插入的时候，都处理数据依赖呢？这里我们可以使用mysql的存储过程。

**mysql存储过程**

首先，我们创建一个函数，在函数中定义我们脚本需要的变量。首先我们需要了解业务需求，居然是创建商品，那么商品名称、编号通常都是唯一的，所以我们将这个数据定义成一个变量，后期做参数化。

BEGIN
    		DECLARE i INT DEFAULT 0;
    		DECLARE goods_name VARCHAR(20);  -- 商品名称
    		DECLARE goods_sn VARCHAR(20);    -- 商品编号
    END

在写脚本之前，我们来思考，每个商品都有自己品牌id和类目id，这个id我们可以每个商品都设置成相同的吗？答案是最好不要，因为我们线上的系统可能会做分布式，如果说我们每个商品都设置相同的类目，可能这些数据都会到同一台机上，那么我们压测的过程中，可能会一直压测一台机器。

那么通常我们品牌id都是数据库自增的，像我们的数据库里面一共有95个品牌，那么我是不是可以写脚本，将品牌id设置成在1-95这个数字之间随机。下面是随机生成1-95之间的脚本。

/*随机生成品牌ID，目前平台现有品牌ID，有1-95*/
    SET brands_id = FLOOR(RAND()*(95-1) + 1);

那么我们来看一下如何编辑插入商品表的脚本，这里只是一个简单的sql脚本，响应学过编程的朋友，这里理解起来不难。

BEGIN
    		DECLARE i INT DEFAULT 0;
    		DECLARE goods_name VARCHAR(20);  -- 商品名称
    		DECLARE goods_sn VARCHAR(20);    -- 商品编号
    		DECLARE good_id INT;             -- 商品id
    		DECLARE product_id INT;          -- 产品id
    		DECLARE brands_id INT;           -- 品牌id
    		DECLARE cats_id INT;             -- 类目id
    		
    
    		
    		/*循环构造商品数据*/
    		WHILE i < 1 DO
    		  
    			/*通过字符串拼接，循环构造商品名称，如test_1*/
    			SET goods_name = CONCAT('test_', i);
    			
    			/*构造订单号*/
    			SET goods_sn = CONCAT('SN202108120000', i);
    			
    			/*随机生成品牌ID，目前平台现有品牌ID，有1-95*/
    			SET brands_id = FLOOR(RAND()*(95-1) + 1);
    			
    			/*类目id，1-93*/
    			SET cats_id = FLOOR(RAND()*(93-1) + 1);
    
    			/*商品表*/
    			INSERT INTO es_goods (
    				NAME,
    				sn,
    				brand_id,
    				cat_id,
    				type_id,
    				goods_type,
    				weight,
    				market_enable,
    				intro,
    				price,
    				cost,
    				mktprice,
    				params,
    				disabled,
    				store,
    				page_title,
    				meta_keywords,
    				meta_description,
    				p1,
    				p2,
    				p3,
    				thumbnail,
    				big,
    				small,
    				original 
    			)
    			VALUES
    				(
    					goods_name,
    					goods_sn,
    					brands_id,
    					cats_id,
    					45,
    					'normal',
    					0.00,
    					1,
    					'<p>我爱金士顿333</p>',
    					9.9,
    					9.9,
    					9.9,
    					'[{
        "name":"基本信息","paramList":[{
        "name":"商品尺寸","value":"5.7 x 1.7 x 1 cm ","valueList":[]},{
        "name":"商品重量","value":"18g","valueList":[]}],"paramNum":2}]',
    					0,
    					100,
    					'金士顿3',
    					'金士顿3',
    					'金士顿3',
    					1,
    					1,
    					1,
    					'https://oss-fg.feng-go.com/assets/pic/2021/07/23ed225b6c90514054bdcd242d8321da01.jpg',
    					'https://oss-fg.feng-go.com/assets/pic/2021/07/23ed225b6c90514054bdcd242d8321da01.jpg',
    					'https://oss-fg.feng-go.com/assets/pic/2021/07/23ed225b6c90514054bdcd242d8321da01.jpg',
    					'https://oss-fg.feng-go.com/assets/pic/2021/07/23ed225b6c90514054bdcd242d8321da01.jpg' 
    				);
    				
    				
    				SET i = i + 1;
    		END WHILE;
    		
    	
    
    
    END

那么我们实现了商品表的插入，如何获取到商品表的goods\_id呢？mysql提供了一个函数，last\_insert\_id();这个函数可以获取到上一条插入sql的id，那这样我们产品表和库存表是不是就可以实现了呢？

SET product_id = last_insert_id();

下面我们来看看如何实现多表同时写入数据：

BEGIN
    		DECLARE i INT DEFAULT 0;
    		DECLARE goods_name VARCHAR(20);  -- 商品名称
    		DECLARE goods_sn VARCHAR(20);    -- 商品编号
    		DECLARE good_id INT;             -- 商品id
    		DECLARE product_id INT;          -- 产品id
    		DECLARE brands_id INT;           -- 品牌id
    		DECLARE cats_id INT;             -- 类目id
    		
    		/*循环构造商品数据*/
    		WHILE i < 1 DO
    		  
    			/*通过字符串拼接，循环构造商品名称，如test_1*/
    			SET goods_name = CONCAT('test_', i);
    			
    			/*构造订单号*/
    			SET goods_sn = CONCAT('SN202108120000', i);
    			
    			/*随机生成品牌ID，目前平台现有品牌ID，有1-95*/
    			SET brands_id = FLOOR(RAND()*(95-1) + 1);
    			
    			/*类目id，1-93*/
    			SET cats_id = FLOOR(RAND()*(93-1) + 1);
    
    			/*商品表*/
    			INSERT INTO es_goods (
    				NAME,
    				sn,
    				brand_id,
    				cat_id,
    				type_id,
    				goods_type,
    				weight,
    				market_enable,
    				intro,
    				price,
    				cost,
    				mktprice,
    				params,
    				disabled,
    				store,
    				page_title,
    				meta_keywords,
    				meta_description,
    				p1,
    				p2,
    				p3,
    				thumbnail,
    				big,
    				small,
    				original 
    			)
    			VALUES
    				(
    					goods_name,
    					goods_sn,
    					brands_id,
    					cats_id,
    					45,
    					'normal',
    					0.00,
    					1,
    					'<p>我爱金士顿333</p>',
    					9.9,
    					9.9,
    					9.9,
    					'[{
        "name":"基本信息","paramList":[{
        "name":"商品尺寸","value":"5.7 x 1.7 x 1 cm ","valueList":[]},{
        "name":"商品重量","value":"18g","valueList":[]}],"paramNum":2}]',
    					0,
    					100,
    					'金士顿3',
    					'金士顿3',
    					'金士顿3',
    					1,
    					1,
    					1,
    					'https://oss-fg.feng-go.com/assets/pic/2021/07/23ed225b6c90514054bdcd242d8321da01.jpg',
    					'https://oss-fg.feng-go.com/assets/pic/2021/07/23ed225b6c90514054bdcd242d8321da01.jpg',
    					'https://oss-fg.feng-go.com/assets/pic/2021/07/23ed225b6c90514054bdcd242d8321da01.jpg',
    					'https://oss-fg.feng-go.com/assets/pic/2021/07/23ed225b6c90514054bdcd242d8321da01.jpg' 
    				);
    				
    			/*获取上一条插入商品表的自增id*/
    		  SET good_id = last_insert_id();
    			
    			
    			/*插入产品表数据，goods_id从上一条sql中获取自增id*/
    			INSERT INTO es_product ( goods_id, NAME, sn, store, price, specs, cost, weight )
    			VALUES
    				(
    					good_id,
    					goods_name,
    					goods_sn,
    					100,
    					9.9,
    					'白色、L',
    					9.9,
    					0.00 
    				);
    				
    			/*获取产品表中的自增id*/	
    			SET product_id = last_insert_id();
    			
    			
    			INSERT INTO es_product_store ( goodsid, productid, depotid, store )
    			VALUES
    				(
    					good_id,
    					product_id,
    					1,
    				100);
    				
    				SET i = i + 1;
    		END WHILE;
    		
    	
    
    END

一个简单的创建商品sql就实现啦~我们来看看数据库插入的数据：![在这里插入图片描述][b7714becc52e47de855e4746a4496f4a.png]  
![在这里插入图片描述][9064733ec8d9459aa0de917d81c6cc9e.png]  
![在这里插入图片描述][d38827c3a0ed496a913de777121f9669.png]

[1e8154a27a8b4112b18def05da3f636e.png]: https://img-blog.csdnimg.cn/1e8154a27a8b4112b18def05da3f636e.png
[8a20d861fe6a47449381da34c75000cd.png]: https://img-blog.csdnimg.cn/8a20d861fe6a47449381da34c75000cd.png
[f95b6933bfe24c3c9cad0bba9d695166.png]: https://img-blog.csdnimg.cn/f95b6933bfe24c3c9cad0bba9d695166.png
[b7714becc52e47de855e4746a4496f4a.png]: https://img-blog.csdnimg.cn/b7714becc52e47de855e4746a4496f4a.png
[9064733ec8d9459aa0de917d81c6cc9e.png]: https://img-blog.csdnimg.cn/9064733ec8d9459aa0de917d81c6cc9e.png
[d38827c3a0ed496a913de777121f9669.png]: https://img-blog.csdnimg.cn/d38827c3a0ed496a913de777121f9669.png