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

SHOW VARIABLES LIKE ‘event_scheduler’；為off,，則執(zhí)行SET GLOBAL event_scheduler = ON;

2：建立一個(gè)商品表和一個(gè)商品點(diǎn)擊記錄表

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：通過一個(gè)存儲過程來模擬商品記錄的插入

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

執(zhí)行：CALL input_commodity_log(商品id，用戶id)

4：再通過一個(gè)存儲過程來完成點(diǎn)擊記錄插入到商品表

count_commodity()

BEGIN

DECLARE c_id INT; #定義商品id

DECLARE cnum INT; #定義商品點(diǎn)擊量

DECLARE isend INT DEFAULT 0; #游標(biāo)狀態(tài)

DECLARE cur CURSOR FOR SELECT commodity_id,SUM(num) FROM commodity_log WHERE DATE=DATE_SUB(CURDATE(),INTERVAL 1 DAY) GROUP BY commodity_id; #將結(jié)果集放進(jìn)游標(biāo),，并且統(tǒng)計(jì)前一天的數(shù)據(jù)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET isend=1; #當(dāng)游標(biāo)結(jié)束的時(shí)候，isend為1

OPEN cur; #開啟游標(biāo)

FETCH cur INTO c_id,cnum; #將游標(biāo)的數(shù)據(jù)賦予預(yù)先定義好的參數(shù)

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; #關(guān)閉游標(biāo)

END

5：通過mysql的事件,，完成每天5點(diǎn)統(tǒng)計(jì)點(diǎn)擊量

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 ;

#備注：該方法主要用于數(shù)據(jù)量大的系統(tǒng),，并且統(tǒng)計(jì)的是前一天的商品訪問數(shù)據(jù)

更多PHP更多知識盡在優(yōu)就業(yè)IT培訓(xùn)：http://www./

相關(guān)文章

• Java 內(nèi)存區(qū)域
• 數(shù)據(jù)庫實(shí)例結(jié)構(gòu)
• Android Builder設(shè)計(jì)模式
• linux 學(xué)習(xí)——Shell入門（一）（萬丈高樓平地起）
• Linux系列之Centos安裝MongoDB手冊
• 值得一看——linux防火墻iptables常用規(guī)則