mysql实操（二）—设计通用的商品系统（一）

设计商品主表

商品主表包含的是商品的通用信息，商品主表叫做prod_main，存储引擎为MyISAM，表结构如下图

商品主表包括了即时更新的字段：id，商品名称，商品所属分类id，商品简介，商品入库时间，商品最后一次修改时间；延时更新（日志更新）的字段：商品总点击量，商品月点击量，商品总销量，商品月销量，商品总评价数，商品月评价数（这些字段的默认值均为0），这些字段可以放到主表当中，也可以单独开一张表。

---

设计商品分类表

商品分类表叫做prod_cate，存储引擎为MyISAM，表结构如下图

商品分类表中包括：id，分类名称，父分类id（顶级分类的父分类id为0）

---

设计商品点击日志表

商品点击日志表叫做prod_clicklog，存储引擎为MyISAM，表结构如下图

商品点击日志表包括：id，商品id，用户ip，用户id（默认为0，没有登录的用户id为0），点击日期，点击次数（默认为1）、

---

使用存储过程模拟读取商品页面

需求：
1. 根据商品id从商品主表中读取商品的所有信息
2. 如果能够读取到，则记录点击日志
先在商品主表中灌入一条测试记录（从淘宝上随便拷贝一条女装的信息）
再到商品分类表中添加一个分类

再创建存储过程sp_load_prod来实现上面的需求

BEGIN    #Routine body goes here...    DECLARE num int DEFAULT 0;    #根据给定的商品id（参数_prod_id）来读取记录    select * from prod_main where id=_prod_id LIMIT 1;    #found_rows()返回最近一条select语句的结果集条数    #如果found_rows()的值为1，则为读取到了这个商品，则要记录一次点击日志，记录的什么呢？实际上就是记录在xxx日期xxx用户，从xxxip地址，读取或访问了xxx次xxx商品。用户id，用户ip，商品id都作为存储过程的IN参数，传递过来。    if FOUND_ROWS()=1 THEN           #当天有没有来自同一个ip的同一个用户访问了同一个商品        select COUNT(*) into num from prod_clicklog where prod_id=_prod_id and user_id=_user_id and user_ip=_user_ip and clickdate=CURRENT_DATE;        #如果有，则点击数加1        if num=1 THEN            update prod_clicklog set clicknum=clicknum+1 where prod_id=_prod_id and user_id=_user_id and user_ip=_user_ip and clickdate=CURRENT_DATE;        #如果没有，则说明来自xxxip地址的xxx用户第一次在当天访问xxx商品，插入这条记录        ELSE            insert into prod_clicklog(prod_id,user_ip,user_id,clickdate) VALUES(_prod_id,_user_ip,_user_id,CURRENT_DATE);        end IF;    end IF;END参数：In _prod_id int,In _user_ip varchar(15),In _user_id int

调用存储过程sp_load_prod(1,’11.11.11.11’,1)，则会显示该商品的所有信息

点击日志表（prod_clicklog）如下

多次调用存储过程sp_load_prod来模拟不同的用户访问了多次商品页面（目前就1个商品），点击日志表（prod_clicklog）如下

---

将prod_clicklog表中的点击量统计到prod_main表中的总点击量，并实现定时执行

创建存储过程sp_count_prod_allclick，使用游标来实现这个需求

BEGIN    #Routine body goes here...    DECLARE issend int DEFAULT 0;    DECLARE p_id int;    DECLARE s_num int;    #DECLARE 游标名 CURSOR FOR select语句;     #记录当天某个商品的点击量select prod_id,sum(clicknum) from prod_clicklog where clickdate=CURRENT_DATE group by prod_id    DECLARE cur CURSOR FOR select prod_id,sum(clicknum) from prod_clicklog where clickdate=CURRENT_DATE group by prod_id;     #这是使用游标常见的异常处理方法    DECLARE CONTINUE HANDLER FOR NOT FOUND SET issend=1;    #打开游标    open cur;    #游标指向第一行，把第一行数据写入预先定义好的变量中    FETCH cur into p_id,s_num;    while issend!=1 DO        #update prod_main表将商品的点击量累加到商品的总点击量上        update prod_main set prod_click_all=prod_click_all+s_num where id=p_id;        #游标指向下一行，把该行数据写入预先定义好的变量中        FETCH cur into p_id,s_num;    end while;    #关闭游标    close cur;END

定时执行这个存储过程，可以采用mysql的事件(event)来执行
1.查看事件是否开启
show VARIABLES like ‘%event%’；

2.set GLOBAL event_scheduler=ON;将event开启

要让事件一直保持开启要修改mysql的配置文件，在[mysqld]的部分添加event_scheduler=ON
3.让sp_count_prod_allclick定期执行，可以借助于navicat来创建事件e_allclick

从2016年11月2日起每天23:59:59执行存储过程，就可以实现定期的更新商品的总点击量了