Mysql 开发实践日记

最新推荐文章于 2021-07-24 16:59:40 发布

原创最新推荐文章于 2021-07-24 16:59:40 发布 · 333 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

本文介绍MySQL中高级技巧的应用，包括执行事件功能的开启、使用游标处理数据、行列转换统计数据、大数据分页处理及通过触发器统计表记录数等实用方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)

5.表记录数过大时，分页时需要统计表总记录数。可采用触发器进行统计各表总记录数！

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