mysql计数器设计_mysql实现计数器 - osc_wa6fkyf0的个人空间 - OSCHINA - 中文开源技术交流社区...

本文转自：https://blog.csdn.net/stevendbaguo/article/details/70889449

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