1、开启执行事件功能:
set global event_scheduler = on;
2、创建行数游标处理
CREATE FUNCTION `NewProc`()
RETURNS int(11)
BEGIN
DECLARE uid VARCHAR(32); -- 声明变量
DECLARE success INT DEFAULT 0; -- 声明变量
DECLARE result VARCHAR(255); -- 声明变量
DECLARE ucount INT DEFAULT 0; -- 声明变量
-- 遍历数据结束标志
DECLARE done BIGINT DEFAULT 0;
declare userCursor cursor
FOR
SELECT uid,success,result from dev.temp_dxg tem;
-- 定义将结束标志绑定到游标
declare continue handler for sqlstate '02000' set done=1;
open userCursor; -- 打开游标
-- 开始循环
fetch userCursor into uid,success,result;
repeat
SELECT COUNT(0) into ucount FROM dxg.rxg_dxg dxg where uid=uid;
IF uid IS NOT NULL AND ucount>0 THEN
IF success=1 THEN
UPDATE dxg.xx SET `check`=1,check_time=UNIX_TIMESTAMP() where uid=uid;
ELSE
UPDATE dxg.xx SET `check`=2,check_msg=result,check_time=UNIX_TIMESTAMP() where uid=uid;
END IF;
END IF;
fetch userCursor into uid,success,result;
until done end repeat; -- 结束循环
close userCursor; -- 关闭游标
RETURN success;
END;
3.行列转换统计数据
根据type类型将每种类型值进行求和统计:
SELECT
uid,
SUM(IF(type = 1, money, 0)) AS 提现,
SUM(IF(type = 11, money, 0)) AS 利息,
SUM(IF(type = 12, money, 0)) AS 管理费
FROM
table
WHERE
AND uptime >=@starttime
AND uptime <=@endtime
GROUP BY uid
4.mysql 大数据分页处理
主键id建立索引,针对id查询单列进行分页处理。
select *from tbname inner join (select id from tbname limit 1000,10) tmptb using(id)
CREATE TABLE `t_total` (
`Id` INT (11) NOT NULL AUTO_INCREMENT,
`tableName` CHAR (25) DEFAULT NULL,
`sum` INT (11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 新增数据时
CREATE TRIGGER t1 AFTER INSERT ON tmptb FOR EACH ROW
BEGIN
DECLARE i INT;
SELECT sum INTO i FROM t_total WHERE tablename = 'tmptb'
SET i = i + 1;
UPDATE t_total SET sum = i WHERE tablename = 'tmptb'
END
-- 删除数据时
CREATE TRIGGER t2 AFTER DELETE ON tmptb FOR EACH ROW
BEGIN
DECLARE i INT;
SELECT sum INTO i FROM t_total WHERE tablename = 'tmptb'
SET i = i - 1;
UPDATE t_total SET sum = i WHERE tablename = 'tmptb'
END