Mysql（9）---纪录一次实际开发过程中用到的复杂存储过程
为了尽可能的还原当时为什么需要用到存储过程，下面我写了个详细的文档，我们可以从需求文档出发来分析。
有关存储过程之前也写了两篇文章来做铺垫。
一、需求背景
一个服装类的app商城，用户会员等级分:普通会员,vip会员,钻石会员。
现在在app上发布一款商品，但发布是可以设置该商品是 所有会员可见,还是 指定会员可见。
现在要见3张表
1、商品表
2、会员表
3、商品关联会员表
这个时候，又有一个优惠券功能,同样可以设置该优惠券是 所有会员可见,还是 指定会员可见。
需要再建两张表
1、优惠券表
2、优惠券关联会员表
然而，这时候又发布一个礼品，同样可以设置该礼品是 所有会员可见,还是 指定会员可见。
又需要建两张表
1、礼品表
2、礼品关联会员表
......
思考: 这里面我们发现可以优化的地方
1、每一次需要用到会员表信息的时候,都需要建一个关联表。
2、在编辑商品的时候,会员名称回显的时候,一般是两步。
1） 需要带着这个商品ID,去商品会员关联表中查询所有会员的ID
2）再拿着这些会员ID,去会员表中获取会员其它信息回显给用户。
优化: 针对上面两点这里面我们可以思考。
1、把N多的关联信息表,变成一张表。
2、写一个公共接口,拿着ID去查会员信息就可以，而不需要管这个ID是商品ID还是礼品ID.....
那么这个时候问题就来了,之前的数据已经保存到相应的关联表中了，如果迁移数据首先考虑下面两个问题
问题
1、如何把之前所有的关联表数据迁移到同一张表中？
2、如果只是把所有关联表数据复制到到同一张表中，ID有没有可能重复？
针对以上,就需要存储过程来完成了，因为前期数据已经保存到不同的关联表中了,现在需要放到同一张表中。
二、表建立
先建表,至于最终需要什么效果，文档也会说明
一共有5张表
1、商品表
说明:key_id字段后面解释作用
/**
* 1、商品表 并插入数据
*/
CREATE TABLE `mall_pro` (
`mall_id` char(32) NOT NULL,
`pro_name` varchar(32) DEFAULT NULL COMMENT '显示名称',
`cash_cost` double(10,1) DEFAULT '0.0' COMMENT '商品价格',
`show_member` int(1) DEFAULT '0' COMMENT '显示 0所有会员 1指定会员',
`status` int(1) DEFAULT '1' COMMENT '状态:1正常 0删除',
`key_id` varchar(32) DEFAULT '0' COMMENT '会员控件表key',
PRIMARY KEY (`mall_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';
INSERT INTO `mall_pro` (`mall_id`, `pro_name`, `cash_cost`, `show_member`, `status`, `key_id`)
VALUES
('1','手表',100.0,0,1,'0'),
('2','手机',888.0,1,1,'0'),
('3','电脑',3888.0,1,1,'0');
2、会员表
CREATE TABLE `member` (
`member_id` char(32) NOT NULL,
`member_grade` varchar(32) DEFAULT NULL COMMENT '会员等级',
`status` int(1) DEFAULT '1' COMMENT '状态:1正常 0删除',
PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员表';
INSERT INTO `member` (`member_id`, `member_grade`, `status`)
VALUES
('1','普通会员',1),
('2','银卡会员',1),
('3','金卡会员',1);
3、商品关联会员表
CREATE TABLE `mall_pro_member` (
`id` char(32) NOT NULL,
`mall_id` varchar(32) DEFAULT NULL COMMENT '商品ID',
`member_id` varchar(32) DEFAULT NULL COMMENT '会员ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品关联会员表';
INSERT INTO `mall_pro_member` (`id`, `mall_id`, `member_id`)
VALUES
('1','2','1'),
('2','2','2'),
('3','3','2');
4、新建统一管理关联会员的表
CREATE TABLE `member_widget_relation` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`key_id` varchar(32) NOT NULL DEFAULT '' COMMENT 'KeyID',
`member_id` varchar(32) NOT NULL DEFAULT '' COMMENT '会员表ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='控件关联会员表';
5、会员控件表key
说明：为什么要建这张表？
上面说过了，如果都直接把所有关联会员表的数据直接复制到member_widget_relation有可能会ID重复的可能。具体步骤下面会说。
CREATE TABLE `member_widget` (
`key_id` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`key_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
需求步骤
1）生成一个随机UUID,当member_widget(会员控件表key)的主键。
2）生成纪录的同时把这个UUID,放到mall_pro(商品表)的key_id字段中。
- 把
mall_pro_member(商品关联会员表)数据迁移到member_widget_relation(新建统一管理关联会员的表)中，只不过把关联的mall_id换成key_id。
4）下次取该商品的会员信息，只要拿着这个key_id到member_widget_relation取就可以了。
三、存储过程实践
下面我直接把我写好的存储过程贴出来。
drop PROCEDURE if exists member_process; #在没有重新申明结束标志之前 都是以 ; 结束。
DELIMITER $ # 申明结束标志
CREATE PROCEDURE member_process ()
BEGIN
# 创建接收游标数据的变量
DECLARE keyId varchar(32);
DECLARE mallId varchar(32);
# 创建结束标志变量
DECLARE done int DEFAULT false;
#创建游标 获取mall_id和key_id的集合
DECLARE coup_cur CURSOR FOR
SELECT mall_id, key_id
FROM mall_pro
WHERE show_member = 1 AND status = 1;
# 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
# 打开游标
OPEN coup_cur;
# 开始循环游标里的数据
loop_a: LOOP
# 根据游标当前指向的一条数据 插入到上面申明的局部变量中
FETCH coup_cur INTO mallId, keyId;
IF done THEN
LEAVE loop_a;
END IF;
# 为什么是0 因为keyId为后面新增字段所以之前的老数据默认都为0,只有新数据key_id才不为0
IF keyId = 0 THEN
# 开启事物 先把数据保存后提交事物
START TRANSACTION;
# 生成随机UUID
SET keyId = REPLACE(UUID(), '-', '');
# 插入member_widget表中
INSERT INTO member_widget (key_id) VALUES (keyId);
# 同时把keyId更新到mall_pro表中
UPDATE mall_pro SET key_id = keyId WHERE mall_id = mallId;
COMMIT;
# 接下来的BEGIN 所做的事情就是把mall_pro_member数据的迁移到member_widget_relation中
BEGIN
DECLARE memberId varchar(32);
DECLARE done1 int DEFAULT false;
# 取出mall_pro_member表中的关联数据
DECLARE coup_cur1 CURSOR FOR
SELECT member_id FROM mall_pro_member WHERE mall_id = mallId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = true;
OPEN coup_cur1;
loop_b: LOOP
FETCH coup_cur1 INTO memberId;
IF done1 THEN
LEAVE loop_b;
END IF;
# 插入到member_widget_relation表中
INSERT INTO member_widget_relation (key_id, member_id) VALUES (keyId, memberId);
END LOOP loop_b;
END;
END IF;
END LOOP loop_a;
END$
# 运行存储过程
call member_process()$
来看运行结果：
1、mall_pro(商品表)
我们可以看到show_member 为1的数据的key_id已经改变。
2、member_widget会员控件表key)
发现生成了两条纪录，而且key_id和mall_pro中的key_id一一对应。
3、member_widget_relation（新建统一管理关联会员的表）
完美的将mall_pro_member(商品关联会员表)中的数据迁移过来，同时mall_pro_id也换成了key_id。
真实环境其实比这个还复杂，不过大致思路就是这样。
完美！结束！谢幕！
只要自己变优秀了，其他的事情才会跟着好起来（少将11）