mysql存储过程跨库建表并抓取数据 TODO 待demo化

淩亂°似流年 2022-02-16 14:51 212阅读 0赞

1. 转自：[https://blog.csdn.net/qq\_35206261/article/details/82691600][https_blog.csdn.net_qq_35206261_article_details_82691600]

一. 简介  
           需求： A库有多张按时间分表创建的表a\_table\_$\{date\}，我想用定时器每天凌晨在B库创建一张和A库昨天的表一样的表b\_table\_$\{date\}，然后将数据抓取过来,然后再对同步过来的表b\_table\_$\{date\}做一次统计汇总插入到B库的另一张表b\_table\_statistic中。 使用存储过程做。可以根据统计时间传参来同步不同时间的数据。

思路：

1. 如果B库不存在统计日期的表b\_table\_$\{date\}则创建一个新表b\_table\_$\{date\}；

2. 开启事务，将B库表b\_table\_$\{date\}的数据清空，方便失败了再次统计；

3. 将A库a\_table\_$\{date\}表的数据同步到B库b\_table\_$\{date\}表；

注意： 在插入的时候如果数据量比较大，可以通过分组进行批量插入来避免事务问题和mysql插入数据量限制问题；

4. 先删除B库b\_table\_statistic表中统计日期的数据；然后再向B库b\_table\_statistic表中插入统计日期的统计数据；

5. 关闭事务；

二. sql建模  
三. 脚本  
   
\-- statistic\_date 统计日期  接收yyyy-MM-dd类型的时间字符串   
CREATE PROCEDURE create\_current\_day\_cdr\_table (IN statistic\_date VARCHAR(64)) BEGIN  
      
    -- 同步t\_cdr表记录需要插入的次数  
    DECLARE CDR\_TABLE\_INSERT\_COUNT INT;  
      
    -- 声明事务  
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK ;  
   
    -- 每次插入的记录数  
    SET @EACH\_INSERT\_SIZE = 1000;  
      
    -- 循环变量   
    SET @i = 1;  
      
   
    -- 根据统计日期拼接话单详情表名称  
    SET @tableName = CONCAT("t\_cdr\_", from\_unixtime( unix\_timestamp(statistic\_date), '%Y%m%d') );    
   
   
    -- 如果统计日期的话单详情表不存在则创建   
    SET @createTableStr = CONCAT(" CREATE TABLE IF NOT EXISTS ", @tableName , "(   
                    \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
                    \`call\_type\` char(2) DEFAULT NULL COMMENT '通话类型',  
                    \`cb\_sessionid\` varchar(64) DEFAULT NULL,  
                    \`busiprefix\` varchar(16) DEFAULT NULL,  
                    \`caller\_brand\` varchar(64) DEFAULT NULL COMMENT '主叫品牌',  
                    \`caller\` varchar(64) DEFAULT NULL COMMENT '主叫号码',  
                    \`calleraccess\` varchar(64) DEFAULT NULL COMMENT '呼入主叫',  
                    \`callee\_brand\` varchar(64) DEFAULT NULL COMMENT '被叫品牌',  
                    \`callee\` varchar(64) DEFAULT NULL COMMENT '被叫号码',  
                    \`calleeaccess\` varchar(64) DEFAULT NULL COMMENT '呼入被叫',  
                    \`calleeoriginal\` varchar(64) DEFAULT NULL COMMENT '被叫裸号',  
                    \`callerip\` varchar(64) DEFAULT NULL COMMENT '主叫IP',  
                    \`in\_gw\_media\_ip\` varchar(64) DEFAULT NULL COMMENT '主叫媒体IP',  
                    \`in\_gw\_media\_isp\` int(11) DEFAULT NULL,  
                    \`callergatewayh323id\` varchar(512) DEFAULT NULL COMMENT '主叫经由网关',  
                    \`callerproductid\` varchar(64) DEFAULT NULL COMMENT '主叫设备',  
                    \`callertogateway\` varchar(64) DEFAULT NULL COMMENT '呼出主叫',  
                    \`calleeip\` varchar(64) DEFAULT NULL COMMENT '被叫信令IP',  
                    \`calleegatewayh323id\` varchar(64) DEFAULT NULL COMMENT '被叫经由网关',  
                    \`calleeproductid\` varchar(128) DEFAULT NULL COMMENT '被叫设备',  
                    \`calleetogateway\` varchar(64) DEFAULT NULL COMMENT '呼出被叫',  
                    \`rtp\_ip\` varchar(64) DEFAULT NULL COMMENT '被叫媒体IP',  
                    \`gd\_gw\_media\_isp\` int(11) DEFAULT NULL,  
                    \`billingmode\` char(1) DEFAULT NULL,  
                    \`calllevel\` char(1) DEFAULT NULL,  
                    \`agentfeetime\` int(11) DEFAULT NULL,  
                    \`starttime\` datetime DEFAULT NULL COMMENT '起始时间',  
                    \`stoptime\` datetime DEFAULT NULL COMMENT '结束时间',  
                    \`pdd\` int(11) DEFAULT NULL COMMENT '接通延迟(秒)',  
                    \`linepdd\` int(11) DEFAULT NULL,  
                    \`waittime\` int(11) DEFAULT NULL COMMENT '接续时长(秒)',  
                    \`holdtime\` int(11) DEFAULT NULL COMMENT '通话时长(秒)',  
                    \`feeprefix\` varchar(64) DEFAULT NULL COMMENT '计费前缀',  
                    \`feetime\` int(11) DEFAULT NULL COMMENT '计费时长(秒)',  
                    \`fee\` bigint(20) unsigned DEFAULT NULL COMMENT '通话费用(元)',  
                    \`package\_id\` int(11) DEFAULT NULL,  
                    \`package\_fee\` bigint(20) unsigned DEFAULT NULL COMMENT '套餐费用(元)',  
                    \`package\_feetime\` int(11) DEFAULT NULL COMMENT '套餐时长(秒)',  
                    \`account\` varchar(32) DEFAULT NULL COMMENT '账户号码',  
                    \`account\_name\` varchar(255) DEFAULT NULL COMMENT '账户名称',  
                    \`enddirection\` char(1) DEFAULT NULL COMMENT '挂断方',  
                    \`endreason\` varchar(255) DEFAULT NULL COMMENT '终止原因',  
                    \`agentpackagefee\` bigint(20) unsigned DEFAULT NULL,  
                    \`agentpackagefeetime\` int(11) DEFAULT NULL,  
                    \`agentpackageid\` int(11) DEFAULT NULL,  
                    \`agentaccount\` varchar(255) DEFAULT NULL,  
                    \`agentname\` varchar(255) DEFAULT NULL,  
                    \`agentfee\` bigint(20) unsigned DEFAULT NULL COMMENT '落地费用(元)',  
                    \`agentfeeprefix\` varchar(64) DEFAULT '0',  
                    \`gatewayrouting\_type\` int(11) DEFAULT NULL,  
                    \`caller\_area\` varchar(32) DEFAULT NULL COMMENT '主叫地区',  
                    \`area\_code\` varchar(32) DEFAULT NULL COMMENT '被叫地区',  
                    \`province\` varchar(64) DEFAULT NULL,  
                    \`city\` varchar(64) DEFAULT NULL,  
                    \`switch\` varchar(64) DEFAULT NULL COMMENT '主叫经由VLS-switch',  
                    \`operators\` int(11) unsigned DEFAULT '0',  
                    \`outgw\_area\` varchar(64) DEFAULT NULL,  
                    \`ingw\_type\` varchar(32) DEFAULT NULL,  
                    \`routetrace\` varchar(320) DEFAULT NULL COMMENT '路由跟踪',  
                    \`call\_encode\` varchar(200) DEFAULT NULL COMMENT '语音编码',  
                    \`fee\_type\` char(1) DEFAULT NULL COMMENT '费率类型',  
                    \`fee\_type\_ee\` char(1) DEFAULT NULL,  
                    PRIMARY KEY (\`id\`),  
                    KEY \`index\_account\` (\`account\`),  
                    KEY \`index\_account\_name\` (\`account\_name\`),  
                    KEY \`index\_agentaccount\` (\`agentaccount\`),  
                    KEY \`index\_caller\` (\`caller\`),  
                    KEY \`index\_callee\` (\`callee\`),  
                    KEY \`index\_callergatewayh323id\` (\`callergatewayh323id\`(64)),  
                    KEY \`index\_calleegatewayh323id\` (\`calleegatewayh323id\`),  
                    KEY \`index\_starttime\` (\`starttime\`),  
                    KEY \`index\_callerproductid\` (\`callerproductid\`),  
                    KEY \`index\_calleeproductid\` (\`calleeproductid\`),  
                    KEY \`index\_city\` (\`city\`),  
                    KEY \`index\_call\_type\` (\`call\_type\`),  
                    KEY \`index\_stoptime\` (\`stoptime\`)  
            )ENGINE=InnoDB AUTO\_INCREMENT=2 DEFAULT CHARSET=utf8");  
          
    PREPARE createtablestmt FROM    @createTableStr;  
    EXECUTE createtablestmt;  
   
   
    -- 开启事务  注意： 必须要在建表语句下面  需要事务的地方进行 如果放在上面 则事务不起作用    
  START TRANSACTION ;  
      
      
    -- 如果统计日期的话单详情表存在数据则先删除  
    SET @delCDRTableStr = CONCAT( " DELETE FROM ", @tableName );  
    PREPARE delCDRTableStmt FROM @delCDRTableStr;  
    EXECUTE delCDRTableStmt;  
      
      
    -- 同步抓取vlsdbcdr库统计日期的话单详情表数据  由于表数据量较大，采取分批插入 将EXECUTE结果赋值到@CDRTableRowCount变量中  
    SET @countCDRTableStr = CONCAT( " SELECT COUNT(1) INTO @CDRTableRowCount FROM vlsdbcdr.", @tableName );  
  PREPARE countCDRTableStmt FROM @countCDRTableStr;  
    EXECUTE countCDRTableStmt;  
      
      
  -- 计算需要插入的次数  
    SELECT CEIL (@CDRTableRowCount / @EACH\_INSERT\_SIZE) INTO CDR\_TABLE\_INSERT\_COUNT;   
      
    WHILE @i <= CDR\_TABLE\_INSERT\_COUNT DO   
      
      
        SET @baseColumn = " \`id\`, \`call\_type\`, \`cb\_sessionid\`, \`busiprefix\`, \`caller\_brand\`, \`caller\`, \`calleraccess\`, \`callee\_brand\`, \`callee\`, \`calleeaccess\`, \`calleeoriginal\`,   
                                                \`callerip\`, \`in\_gw\_media\_ip\`, \`in\_gw\_media\_isp\`, \`callergatewayh323id\`, \`callerproductid\`, \`callertogateway\`, \`calleeip\`, \`calleegatewayh323id\`, \`calleeproductid\`,  
                                                \`calleetogateway\`, \`rtp\_ip\`, \`gd\_gw\_media\_isp\`, \`billingmode\`, \`calllevel\`, \`agentfeetime\`, \`starttime\`, \`stoptime\`, \`pdd\`, \`linepdd\`, \`waittime\`, \`holdtime\`,  
                                                \`feeprefix\`, \`feetime\`, \`fee\`, \`package\_id\`, \`package\_fee\`, \`package\_feetime\`, \`account\`, \`account\_name\`, \`enddirection\`, \`endreason\`, \`agentpackagefee\`,  
                                                \`agentpackagefeetime\`, \`agentpackageid\`, \`agentaccount\`, \`agentname\`, \`agentfee\`, \`agentfeeprefix\`, \`gatewayrouting\_type\`, \`caller\_area\`, \`area\_code\`,  
                                                \`province\`, \`city\`, \`switch\`, \`operators\`, \`outgw\_area\`, \`ingw\_type\`, \`routetrace\`, \`call\_encode\`, \`fee\_type\`, \`fee\_type\_ee\` ";  
                                                  
        SET @insertCDRStr = CONCAT(" INSERT INTO ", @tableName, "(", @baseColumn, ")", " SELECT ", @baseColumn, " FROM vlsdbcdr.", @tableName,   
                                                                " LIMIT ", (@i - 1) \* @EACH\_INSERT\_SIZE, ",", @EACH\_INSERT\_SIZE );  
   
        PREPARE insertCDRstmt FROM    @insertCDRStr;  
        EXECUTE insertCDRstmt;  
      
        SET @i = @i + 1;  
          
    END WHILE;  
      
      
      
    -- 删除销售客户账单表已存在的统计日期数据    
    DELETE FROM t\_sale\_customer\_day\_cost WHERE statistical\_time = statistic\_date;  
      
      
    -- 向销售客户日账单表插入统计日期的数据    
    SET @insertSCDCStr = CONCAT( "  
      
            INSERT INTO osp.t\_sale\_customer\_day\_cost  
                ( statistical\_time, customer\_id, account\_id, service\_type, fee\_type, call\_times, caller, caller\_area, feetime, fee, create\_time)  
            SELECT '", statistic\_date,"' AS statistical\_time, (SELECT out\_id FROM t\_account AS A WHERE A.account\_id = CDR.account AND A.type = 0) AS customer\_id,   
                    CDR.account, '5', CDR.fee\_type, count(1) AS call\_count, CDR.caller, CDR.caller\_area, SUM(CDR.feetime / 60), SUM(CDR.fee), NOW()   
            FROM ",  
                @tableName, " AS CDR  
            WHERE  
                CDR.call\_type = '5'  
            GROUP BY  
                customer\_id, CDR.account, CDR.call\_type, CDR.fee\_type ");  
   
    PREPARE insertSCDCstmt FROM    @insertSCDCStr;  
    EXECUTE insertSCDCstmt;  
   
   
  -- 提交事务  
    COMMIT ;  
   
END;  
   
   
四 定时器代码  
package com.yzx.osp.modular.task.controlller;  
   
import com.yzx.osp.common.util.DateUtil;  
import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.jdbc.core.JdbcTemplate;  
import org.springframework.scheduling.annotation.Scheduled;  
import org.springframework.stereotype.Component;  
   
import java.util.Date;  
   
/\*\*  
 \* @author yuanshushu  
 \* @date 2018/9/13  
 \* @description 话单详情定时任务类  
 \*/  
@Component  
public class CdrDateTask \{  
   
    @Autowired  
    private JdbcTemplate jdbcTemplate;  
   
    @Scheduled(cron = "0/10 \* \*  \* \* ?")  
    public void syncTCdrDateData() \{  
   
        try \{  
   
            // 按日期生成话单详情表  
            this.jdbcTemplate.execute("call create\_current\_day\_cdr\_table()");  
   
        \} catch (Exception e) \{  
            e.printStackTrace();  
        \}  
   
   
    \}  
   
   
\}  
五. 存储过程DEMO     小  
5.1 遍历分批插入  
   
CREATE PROCEDURE test () BEGIN  
   
   
    DECLARE CDR\_TABLE\_ROW\_COUNT BIGINT;  
    DECLARE CDR\_TABLE\_INSERT\_COUNT INT;  
   
  -- 声明事务  
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK ;  
      
    SET @EACH\_INSERT\_SIZE = 100;  
    SET @i = 1;  
      
      
      
    SELECT count(1) FROM t\_cdr\_20180811 INTO CDR\_TABLE\_ROW\_COUNT;  
    SELECT CEIL (CDR\_TABLE\_ROW\_COUNT / @EACH\_INSERT\_SIZE) INTO CDR\_TABLE\_INSERT\_COUNT;  
   
      
    START TRANSACTION ;  
      
   
    WHILE @i <= CDR\_TABLE\_INSERT\_COUNT DO  
          
   
        -- 拼接插入sql   注意： mysql  limit m,n  m为开始行数, n为查询行数    
        SET @insertStr = CONCAT( "  
                        INSERT INTO t\_cdr\_20180812   
                                (call\_type, cb\_sessionid)  
                        SELECT  
                                call\_type, cb\_sessionid   
                        FROM  
                                t\_cdr\_20180811  
                        limit ", (@i - 1) \* @EACH\_INSERT\_SIZE, ",", @EACH\_INSERT\_SIZE );  
                          
        PREPARE insertStrStmt FROM @insertStr;  
        EXECUTE insertStrStmt;   
                          
        SET @i = @i + 1;  
          
    END WHILE;  
      
    COMMIT ;  
   
END;  
   
 5.2 获取EXECUTE执行结果  
   
\-- statistic\_date 统计日期  接收yyyy-MM-dd类型的时间字符串   
CREATE PROCEDURE create\_current\_day\_cdr\_table (IN statistic\_date VARCHAR(64)) BEGIN  
      
   
      
    SET @tableName = 't\_cdr\_20180811';  
      
      
    SET @countCDRTableStr = CONCAT( " SELECT COUNT(1) INTO @STABLE\_CDR\_ROW\_COUNT FROM vlsdbcdr.", @tableName);  
    PREPARE countCDRTableStmt FROM @countCDRTableStr;  
    EXECUTE countCDRTableStmt;  
      
    SELECT @STABLE\_CDR\_ROW\_COUNT;  
   
END;  
   
\---------------------   
作者：请叫我猿叔叔   
来源：CSDN   
原文：https://blog.csdn.net/qq\_35206261/article/details/82691600   
版权声明：本文为博主原创文章，转载请附上博文链接！

[https_blog.csdn.net_qq_35206261_article_details_82691600]: https://blog.csdn.net/qq_35206261/article/details/82691600