通过游标高效的完成商品点击量的统计

1：首先需要mysql的事件支持

SHOW VARIABLES LIKE 'event_scheduler'；为off，则执行SET GLOBAL event_scheduler = ON;

2：建立一个商品表和一个商品点击记录表

CREATE TABLE `commodity` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`price` decimal(8,2) NOT NULL DEFAULT '0.00',
`title` varchar(20) NOT NULL DEFAULT '',
`all_click` int(11) NOT NULL DEFAULT '0',
`date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `commodity_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`commodity_id` int(11) NOT NULL DEFAULT '0',
`user_id` int(11) NOT NULL DEFAULT '0',
`num` int(11) NOT NULL DEFAULT '1',
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3：通过一个存储过程来模拟商品记录的插入

input_commodity_log（IN _commodity_id INT,IN _user_id INT）#注：_commodity_id 为商品id，_user_id为用户id

BEGIN
set @num=0;
set @log_num=0;
select * from commodity where id=_commodity_id limit 1;
set @num=FOUND_ROWS();
if @num=1 THEN
SELECT count(*) INTO @log_num from commodity_log WHERE commodity_id=_commodity_id and user_id=_user_id and date=CURRENT_DATE();
if @log_num>0 THEN
UPDATE commodity_log SET num=num+1 WHERE commodity_id=_commodity_id and user_id=_user_id and date=CURRENT_DATE();

ELSE

INSERT INTO commodity_log (commodity_id,user_id,date) VALUES (_commodity_id,_user_id,CURRENT_DATE());

END IF;
END IF;

END

执行：CALL input_commodity_log(商品id，用户id)

 

4：再通过一个存储过程来完成点击记录插入到商品表

count_commodity()

BEGIN
DECLARE c_id INT; #定义商品id
DECLARE cnum INT; #定义商品点击量
DECLARE isend INT DEFAULT 0; #游标状态
DECLARE cur CURSOR FOR SELECT commodity_id,SUM(num) FROM commodity_log WHERE DATE=DATE_SUB(CURDATE(),INTERVAL 1 DAY) GROUP BY commodity_id; #将结果集放进游标，并且统计前一天的数据
DECLARE CONTINUE HANDLER FOR NOT FOUND SET isend=1; #当游标结束的时候，isend为1
OPEN cur; #开启游标
FETCH cur INTO c_id,cnum; #将游标的数据赋予预先定义好的参数
WHILE isend!=1 do

UPDATE commodity SET all_click=cnum+all_click where id=c_id;
FETCH cur INTO c_id,cnum;
END WHILE;
CLOSE cur; #关闭游标

END 

5：通过mysql的事件，完成每天5点统计点击量

DELIMITER $$

ALTER DEFINER=`root`@`127.0.0.1` EVENT `update_clicknum` ON SCHEDULE EVERY 1 DAY STARTS '2017-03-22 05:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL count_commodity$$

DELIMITER ;

 

#备注：该方法主要用于数据量大的系统，并且统计的是前一天的商品访问数据

 

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有