Mysql subversion Practice Notes (5) -- product system design (2): Regularly update the total clicks of products, and regularly update the total clicks

Mysql subversion Practice Notes (5) -- product system design (2): Regularly update the total clicks of products, and regularly update the total clicks

Continue to go back to instructor Shen's MYSQL subversion practice. First, let's review the content of the previous lesson. Please take a look at the stored procedure written in the previous lesson.

To open the prod_clicklog table, we only need to store the part of the date (excluding the hour, minute, and second) in the database. If the same date has the same user clicking the product, we will increase the value by 1. Otherwise, this Click Log table will be too large

Next we will modify the prod_clicklog table:

　　

Change the field clickdate to the date type and add the clicknum field. The default value is 1.

Next, let's modify the Stored Procedure sp_load_prod in the previous lesson:

Begin select * FROM prod_main WHERE prod_id = _ prod_id LIMIT 1; SET @ num = FOUND_ROWS (); SET @ c = 0; IF @ num = 1 THEN # indicates that the product is successfully retrieved SELECT count (*) INTO @ c FROM prod_clicklog WHERE prod_id = _ prod_id AND user_ip = _ user_ip AND user_id = _ userid AND clickdate = CURRENT_DATE; if @ c> 0 THEN # indicates that you have already clicked, add 1 UPDATE prod_clicklog SET clicknum = clicknum + 1 WHERE prod_id = _ prod_id AND user_ip = _ user_ip AND user_id = _ userid AND clickdate = CURRENT_DATE to clicknum; ELSE # insert into prod_clicklog (prod_id, user_ip, user_id, clickdate) VALUES (_ prod_id, _ user_ip, _ user_id, CURRENT_DATE); end if; END

Next we will create a new query and call sp_load_prod (1 );

　　

Data in the prod_clicklog table:

　　

Then we execute the above Stored Procedure multiple times:

　　

OK. There is no problem with this idea.

The problem arises. When will the total clicks in the product table be updated?

1. completed through web language, such as regular execution of a program

2. In this lesson, we will talk about how to execute mysql events.

Run the following query statement first:

SELECT prod_id,sum(clicknum) FROM prod_clicklog GROUP BY prod_id;

In this way, a bunch of IDs and the total number of executions can be retrieved.

　　　

Therefore, we write a stored procedure count_prod_click to implement the cursor,

Begin declare isend int DEFAULT 0; DECLARE pid int; # represents the product id declare cnum int; # represents the total number of clicks 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; # isend = 1 open cur when the cursor ends; # open the cursor FETCH cur into pid, cnum; # pre-defined variables, retrieve the first line and put the content in the pre-defined variable while isend! = 1 do update prod_main SET prod_click_all = cnum WHERE prod_id = pid; # UPDATE the total clicks of the commodity master table # Here you can write the Business Code FETCH cur into pid, cnum; end while; close cur; END

Old Rules: Create a query

CALL count_prod_click;

Refresh the master table. We can see that the prod_click_all field has been updated.

　　

Create an event using mysql, and make statistics and updates every 10 seconds.

First, check whether the mysql event is enabled.

show VARIABLES like '%event%'

　　

If not enabled:

SET GLOBAL event_scheduler=ON;

OK. Now we create an event event_count_prodclick:

　　

　　

 

Then, we can properly load data based on the latency.

 

Copyright statement: the note organizer loves freedom and advocates sharing. However, this note is sourced from www.jtthink.com (where programmers are at ), instructor Shen Yi's "practical course on web-level mysql disruption". If you need to repost the course, please respect the teacher's work and retain the instructor's signature and course source address. Previous lesson: mysql subversion Practice Notes (4) -- product system design (1): Product master table Design