Correct batch update counters in MySQL Databases

The following articles mainly describe the actual operation steps for performing batch update counters in the MySQL database. Assume that there is a counter in our MySQL database, and we need to re-count this counter, so how should we set it?

For example, in a mall, every time a product is sold, a flow occurs. Then, we need to know that each flow is the first time the product was sold. This may not be clear, let me take a detailed data example.

 
 

  
  
recordID,productID,productType,sellDate,counter  
  
  
1, 1, 1, '2010-1-15 15:20:10' 0  
  
  
2, 1, 2, '2010-1-15 15:20:10' 1  
  
  
3, 2, 1, '2010-1-15 15:20:10' 0  
  
  
4, 2, 1, '2010-1-15 15:20:10' 1 
 
 

The above data is a bit of data, including the recorded serial number, product number, product type, sales time, and counter. In general, we will first think of auto-increment counters, but it is certainly impossible to use auto-increment counters.

At first, I tried to use the following code:

 
 

  
  
update t_product set t_counter = (select max(counter)   
  
  
from t_product where productid = 1 and productType = 1) + 1   
  
  
where where productid = 1 and productType = 1 
 
 

However, MySQL reports an error and checks the Internet. MySQL database does not support this method. I am not familiar with MySQL. I remember that I used a cursor on SQL server before, and then I tried to find the cursor. After n times of google and Baidu, I finally solved this problem.

I am a layman in MySQL, and I don't even know how to comment the annotations. Now I finally know there are three types of writing methods, and -- the annotations are left blank, so I can't say anything about the performance of code writing. I just implemented this function and hoped to be useful to friends who have the same requirements.

Well, I also learned that declare can only be written at the beginning.

Check the final code:

 
 

  
  
CREATE PROCEDURE p_resetCounter ()  
  
  
BEGIN  
  
  
DECLARE productID INT;  
  
  
DECLARE type INT;  
  
  
DECLARE tmpCount INT;  
  
  
DECLARE stopFlag int; 
 
 

Use cursor
 

 
 

  
  
DECLARE cur cursor for SELECT COUNT(*), productID, productType FROM  
  
  
t_product GROUP BY productID, productType;  
  
  
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1; 
 
 

If no record is found, set stopFlag to 1.

Define variables and create temporary tables
 

 
 

  
  
CREATE TEMPORARY TABLE tmp_Counter(  
  
  
recordID int not null,  
  
  
Counter int not null  
  
  
)TYPE = HEAP; 
 
 

Open cursor
 

 
 

  
  
open cur;  
  
  
REPEAT  
  
  
fetch cur into tmpCount, productID, type;  
  
  
SET @id = -1;  
  
  
INSERT INTO tmp_Counter   
  
  
(SELECT recordID, (@id := @id + 1) counter   
  
  
from t_product WHERE productIDproductID = productID AND productType = type)  
  
  
ORDER BY ts_Date ASC;  
  
  
UNTIL stopFlag = 1 
  
  
END REPEAT;  
  
  
close cur; 
 
 

Close cursor
 

 
 

  
  
UPDATE t_product, tmp_Counter SET counter = tmp_Counter.Counter   
  
  
WHERE recordID = tmp_Counter.recordID;  
  
  
-- SELECT * FROM tmp_Counter;  
  
  
DROP TABLE tmp_Counter; 
 
 

Delete temporary table
 

 
 

  
  
END; 
 
 

The above content is an introduction to how to update counters in batches in the MySQL database. I hope you will have some gains.