【MedusaSTears】MySQL存储过程循环查询数据库的每个表每个月新增的数据条数(未知总量的数据库及表)-CSDN博客

本文链接：https://blog.csdn.net/MedusaSTears/article/details/103749939

吃水不忘挖井人系列:

一.业务需求

二.注意事项(知识点)

三. 如何获取未知的数据库的dbName 的 list?

四. 已经有了目标库的list,如何利用存储过程查询出指定月份的新增数据条数?

吃水不忘挖井人系列:

mysql存储过程循环遍历sql结果集，并执行动态sql

hibernate如何调用存储过程

一.业务需求

1.在同一个mysql连接信息中,有多个不同的数据库实例,表名诸如 test_aa,test_bb,test_cc....

2.上面提到的数据库数量不确定,每个数据库中的表数量不确定

3.需要统计test_aa数据库中,所有表,在12月(或者其他某月份)新增的数据总条数(根据创建时间CD_TIME字段查询)

4.由于数据库不确定,而且有多个数据库实例,没法简单有效的从java的dao层面解决,只能用sql直接查询出来

5.该数据用于前端页面图表化展示,比如test_aa是工厂订单库,表test_aa.cup是茶杯的订单表,test_aa.desk是桌子的订单表,需要查询茶杯表每个月新增订单总数 + 桌子表每个月新增订单总数: 即 test_aa这个数据库,每个月新增的订单数

二.注意事项(知识点)

1.DISTINCT 和 group by 去重

2.month()函数,year()函数

3.concat()和concat_ws()函数

4.游标的使用

5.游标和循环的配合 ( 循环内一定要有取下一次游标的代码,否则死循环 )

6.以下代码为什么不是 set @resultsNum = @resultsNum + @resultTemp;

或者 set resultsNum = resultsNum + resultTemp;

为什么@resultTemp不用声明,不需要初始化?

-- 将本次循环查询的结果赋值累加给out参数
set resultsNum =  resultsNum + @resultTemp;

三. 如何获取未知的数据库的 dbName 的 list?

代码里的 test_ 你可以换成你的业务数据库, 这句代码是利用mysql系统表information_schema.TABLES 查询出数据库的信息

有关 information_schema.TABLES 此表可以自行百度里面的含义

SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA LIKE 'test_%' ");

四. 已经有了目标库的list,如何利用存储过程查询出指定月份的新增数据条数?

java的dao层的代码如下: 需要注意的是,我把存储过程放在了 test_aa 这个数据库里面

所以是 test_aa.query_specifiedDB_year_month_newDataNums(year,month,@variable)

public String getAmount(String dbName, Integer year, Integer month) {
        StringBuilder sql = new StringBuilder("CALL test_aa.query_specifiedDB_year_month_newDataNums('");
        sql.append(dbName).append("',").append(year).append(",").append(month);
        sql.append(",@variable)");

        // 调用存储过程需要注意 : 1.sql不能有分号 2.需要用这种格式 {Call proc()} 
        Session session =HibernateSessionFactory.getSession();  
        Query dbList = session.createSQLQuery("{" + sql.toString() + "}");
        List list =dbList.list();
        session.close();  

        if (CollectionUtils.isEmpty(list)) {
            return "0";
        }
        return list.get(0).toString();
    }

以下是 test_aa.query_specifiedDB_year_month_newDataNums 创建的代码

如果用Navicat创建的话,去掉第一行,参数在第一行()里面,保存的时候名字保存query_specifiedDB_year_month_newDataNums

CREATE DEFINER = CURRENT_USER PROCEDURE `query_specifiedDB_year_month_newDataNums`(IN `dbName` varchar(100),IN `qYear` int,IN `qMonth` int,OUT `resultsNum` int)
BEGIN
  -- 需要执行的SQL语句
  DECLARE v_sql_1 text;
  -- 定义变量
  DECLARE tableName VARCHAR(100); 
  -- 定义游标遍历时，作为判断是否遍历完全部记录的标记
  DECLARE num INT DEFAULT 0;
  -- 定义游标，并将sql结果集赋值到游标中
  DECLARE resultList CURSOR FOR SELECT CONCAT_WS(".",dbName,temp.tab) 
    FROM (
      SELECT
        TABLE_NAME AS 'tab'
      FROM
        information_schema.TABLES
      WHERE
        TABLE_SCHEMA = dbName
    ) AS temp;
  -- 声明当游标遍历完全部记录后将标志变量置成某个值
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET num=1;
  SET resultsNum = 0; -- 初始化返回值
  -- 打开游标
  OPEN resultList;
  -- 将游标中的值赋值给变量，要注意sql结果列的顺序
  FETCH resultList INTO tableName;
    -- while循环
    WHILE num <> 1 DO
      -- 动态拼接sql并赋值v_sql_1
      SET v_sql_1 = CONCAT('SELECT count(*) INTO @resultTemp FROM ',tableName,' WHERE YEAR( ',tableName,'.CD_TIME) = ',qYear,' AND MONTH(',tableName,'.CD_TIME) = ',qMonth,'');
      -- 需要用@转换下,直接v_sql_1执行不了
      SET @sql_1 = v_sql_1;
      -- 预处理需要执行的动态SQL，其中stmt是一个变量
      PREPARE stmt1 FROM @sql_1;  
      -- 执行SQL语句
      EXECUTE stmt1; 
      -- 释放掉预处理段   
      DEALLOCATE PREPARE stmt1;
      -- 将本次循环查询的结果赋值累加给out参数
      set resultsNum =  resultsNum + @resultTemp;

-- SELECT dbName,tableName,@resultTemp,resultsNum; -- 用来监测数据

      -- 一次循环结束再次取出下一个游标值
      FETCH resultList INTO tableName;
    END WHILE;
  -- 关闭游标
  CLOSE resultList;
  -- 显示最终结果
  SELECT resultsNum;
END;

Navicat 运行的时候,输入以下参数: