MySQL Subversion practical Note (v)--Commodity system Design (II): Regular update of total product hits

　　Continue to go back to teacher Shen's MySQL subversive combat, first of all to review the contents of the previous lesson, please look at the next lesson written stored procedures.

Open the Prod_clicklog table, we just put the date (does not include time and seconds) part of the database, if the same date has the same user click on the product, then we value +1, otherwise, this click Log table will be too large

Below we will modify the Prod_clicklog table below:

　　

　　Modify the field clickdate to date type, increase the Clicknum field, and default to 1.

Then we sp_load_prod the stored procedure of the previous lesson to modify it:

BEGIN    SELECT *  fromProd_mainWHEREprod_id=_prod_id LIMIT1; SET @num=found_rows (); SET @c=0; IF @num=1  Then#代表商品取出成功SELECT Count(*) into @c  fromProd_clicklogWHEREprod_id=_prod_id andUser_ip=_user_ip and user_id=_userid andClickdate=current_date; if @c>0  Then#代表已经点击过, as long as the clicknum is accumulated 1UPDATEProd_clicklogSETClicknum=Clicknum+1 WHEREprod_id=_prod_id andUser_ip=_user_ip and user_id=_userid andClickdate=current_date; ELSE#新增点击日志INSERT  intoProd_clicklog (PROD_ID,USER_IP,user_id, clickdate)VALUES(_prod_id,_user_ip,_user_id,current_date); END IF; END IF;END

　　Then we still create a new query, call Sp_load_prod (1);

　　

　　Prod_clicklog the data in the table:

　　

We then execute the above stored procedure several times:

　　

　OK, there is no problem with this idea.

Then the problem comes. So when is the total number of clicks in the Product list updated?

1, through the Web language to complete, such as write a program timed execution

2, this lesson to talk about is through the MySQL event to carry out.

We first execute the following query statement:

SELECT prod_id,sumfromGROUP by prod_id;

　So that you can take out a bunch of IDs, and execute the total number of bars

　　　

　　So we write a stored procedure Count_prod_click, to implement the cursor,

BEGIN    DECLAREIsendint DEFAULT 0; DECLAREPidint; #代表商品IDDECLARECnumint; #代表点击量总数DECLARECurCURSOR  for SELECTPROD_ID,sum(Clicknum) fromProd_clicklogGROUP  byprod_id; DECLARE CONTINUEHANDLER for  notFOUNDSETIsend=1; #游标结束时令isend=1    Opencur; #打开游标FETCHCur intoPid,cnum; #预先定义好的变量, take out the first line and put the contents into a predefined variable whileIsend!=1 DoUPDATEProd_mainSETProd_click_all=CnumWHEREprod_id=# Update the total number of hits for the product master table #这里可以写业务代码FETCHCur intoPid,cnum; End  while; Closecur;END

　　The usual, we create a new query

Call Count_prod_click;

Then refresh the main table and you can see that the Prod_click_all field has been updated

　　

　　end up using MySQL to create a new event that counts and updates every 10 seconds

Let's start by querying if the MySQL event is turned on.

 like ' %event% '

　　

If it is not turned on:

SET GLOBAL Event_scheduler=on;

　　OK, now let's create a new event Event_count_prodclick:

　　

　　

Then we based on this delay loading in the actual combat reasonable application.

Copyright Notice: NOTE-taker fugitive pawns love freedom, advocating sharing. But this note stems from the www.jtthink.com (programmer in the awkward way) Judging teacher's "web-level MySQL subversive combat course." For reprint please respect the teacher Labor, retain the judging teacher attribution and course source address. previous lesson:MySQL Subversion practical notes (iv)--Commodity system design (i): Product master table Design

MySQL Subversion practical Note (v)--Commodity system Design (II): Regular update of total product hits