MySQL实现计数器的表设计及实现

曾经终败给现在 2022-06-17 14:23 160阅读 0赞

如果是在非常高的并发之下，还是建议用内存数据库redis去实现计数的功能。如果不是那么高的并发，用表实现就可以。

DROP TABLE access\_counter;

CREATE TABLE access\_counter(  
  cnt  INT UNSIGNED NOT NULL   
);  
  
INSERT INTO access\_counter VALUES(0);  
UPDATE access\_counter SET cnt=cnt+1;  
SELECT \* FROM access\_counter;  
**--上面的方法在高并发性上有问题，会产生大量的锁**  
  
DROP TABLE access\_counter;  
CREATE TABLE access\_counter(  
 solt INT NOT NULL PRIMARY KEY,  
 cnt INT NOT NULL  
);  
  
DELIMITER $  
DROP PROCEDURE IF EXISTS \`proc1\`$  
CREATE PROCEDURE \`proc1\`()  
BEGIN  
  DECLARE i INT;   
    SET i=0;   
    WHILE i<100 DO   
      INSERT INTO access\_counter VALUES(i,0);  
      SET i=i+1;   
    END WHILE;       
 END$  
DELIMITER ;  
  
CALL proc1();  
  
SELECT \* FROM access\_counter;  
  
  
**\--这样就随机选择一个solt进行更新  
\--RAND()函数调用可以在0和1之间产生一个随机数**  
UPDATE access\_counter SET cnt=cnt+1 WHERE solt=FLOOR(RAND()\*100);  
  
**--如果每隔一天开始一个新的计数器，那方法是：**  
DROP TABLE access\_counter;  
CREATE TABLE access\_counter(  
 access\_day DATE NOT NULL,  
 solt INT NOT NULL,  
 cnt INT NOT NULL,  
 PRIMARY KEY(access\_day,solt)  
);  
\--DUPLICATE KEY UPDATE 跟Oracle的merge INTO 类似  
INSERT INTO access\_counter(access\_day,solt,cnt)  
  VALUES(CURRENT\_DATE,FLOOR(RAND()\*100),1)  
  ON DUPLICATE KEY UPDATE cnt=cnt+1;  
    
**--如果不想有太多数据，那就每天删一次**  
UPDATE access\_counter AS a INNER JOIN  
(SELECT access\_day,SUM(cnt) AS cnt,MIN(solt) AS msolt FROM access\_counter  
GROUP BY access\_day) AS b   
USING (access\_day)  
SET a.cnt =  IF(a.\`solt\`= b.msolt,b.cnt,0),  
    a.solt = IF(a.\`solt\`= b.msolt,0,a.\`solt\`);  
  
DELETE FROM access\_counter WHERE solt<>0 AND cnt=0;