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

   如果是在非常高的并发之下，还是建议用内存数据库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;