商品系统设计(二):定时更新商品总点击量

紧接着上节课

1、其实我们只要把日期(不含时分秒)的部分保存在数据库中
2、如果同一日期有相同用户点击商品，那么我们对其数值+1
3、否则的话，这张日志表会过于庞大
我们需要修改prod_clicklog表

CREATE TABLE `prod_clicklog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `prod_id` int(11) DEFAULT NULL,
  `user_ip` varchar(15) DEFAULT NULL,
  `user_id` int(11) DEFAULT '0',
  `clickdate` date DEFAULT NULL,
  `clicknum` int(11) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4;

然后修改存储过程sp_load_prod:

BEGIN
  SET @num=0;
  SET @c=0;
  SELECT * FROM prod_main where prod_id=_prod_id LIMIT 1;
  SET @num=FOUND_ROWS();
  IF @num=1 THEN #商品取出成功
    SELECT count(*) INTO @c FROM prod_clicklog WHERE prod_id=_prod_id AND user_ip=_user_ip AND user_id=_user_id AND clickdate=CURRENT_DATE;
    IF @c>0 THEN #代表之前点击过，就对clicknum累加1
    UPDATE prod_clicklog SET clicknum=clicknum+1 WHERE prod_id=_prod_id AND user_ip=_user_ip AND user_id=_user_id AND clickdate=CURRENT_DATE;
    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

这样，调用这个存储过程:

CALL sp_load_prod(1,'192.168.88.88',12);
CALL sp_load_prod(1,'192.168.88.88',12);

然后prod_clicklog表中字段clicknum就是2
意思是同一天同一个商品的点击量都累加到字段clicknum，避免每次点击都插入一条新纪录，造成数据库的庞大。

如何定时更新商品主表中的字段

商品主表的总点击量，什么时候更新呢？
1、通过web语言来完成，比如写个程序定时执行
2、通过mysql的事件来完成(本课程通过这个方式 )

写个游标

DECLARE cur CURSOR FOR xxx(sql语句)
DECLARE CONTINUE HANDLER FOR NOT FOUNT SET isend=1; //游标结束时令issend=1
open cur; //打开游标
fetch cur into xx,xx,xx //预先定义好的变量
  while isend !=1 do

  end while
close cur; //关闭游标  

新建一个存储过程:

BEGIN
  DECLARE isend INT DEFAULT 0;
  DECLARE pid INT;#商品ID 
  DECLARE cnum INT;#总点击量
  DECLARE cur CURSOR FOR SELECT prod_id,SUM(clicknum) FROM prod_clicklog GROUP BY prod_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET isend=1;#游标结束
  OPEN cur;#打开游标
    FETCH cur INTO pid,cnum;#取出第一行并把数据放入预先定义的变量
    WHILE isend !=1 DO
    END WHILE;
  CLOSE cur;#关闭游标
END;

这个游标到所要的结果，类似下面

商品ID和总点击量。

然后我们要考虑如何把数据同步到商品主表了.
完成我们的存储过程count_prod_click：

BEGIN
  DECLARE isend INT DEFAULT 0;
  DECLARE pid INT;#商品ID 
  DECLARE cnum INT;#总点击量
  DECLARE cur CURSOR FOR SELECT prod_id,SUM(clicknum) FROM prod_clicklog GROUP BY prod_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET isend=1;#游标结束
  OPEN cur;#打开游标
    FETCH cur INTO pid,cnum;#取出第一行并把数据放入预先定义的变量
    WHILE isend !=1 DO
      UPDATE prod_main SET prod_click_all=prod_click_all+cnum WHERE prod_id=pid;#更新商品主表的总点击量字段
      FETCH cur INTO pid,cnum;
    END WHILE;
  CLOSE cur;#关闭游标
END

执行这个存储过程：

CALL count_prod_click();

执行完后，我们去商品主表，发现总点击量prod_click_all 有值了。

说明这个存储过程实现了我们需要的功能。

下面就要考虑如何利用msyql来定时执行这个存储过程了？
1.首先查看我们的mysql的事件机制是否被打开
默认应该是关闭的(OFF)

SHOW VARIABLES LIKE '%event_scheduler%';

2.如果关闭，需要打开

SET GLOBAL event_scheduler=ON;

3.然后就可以新建mysql事件了(我们这里为了方便使用msyql客户端)

最后我们来新建一个事件

每隔10秒统计一下并更新。
注意：实际情况下，我们可以设定在每天晚上几点几分把当天获取到的总数累加到商品表的总点击量。