Mysql 存储过程遍历无法获取值的坑

最新推荐文章于 2024-09-03 22:53:12 发布

万物皆导_

最新推荐文章于 2024-09-03 22:53:12 发布

阅读量803

点赞数 3

分类专栏： DB mysql 存储过程文章标签： mysql 存储过程

本文链接：https://blog.csdn.net/u013930425/article/details/100552093

版权

DB 同时被 3 个专栏收录

4 篇文章 0 订阅

订阅专栏

mysql

1 篇文章 0 订阅

订阅专栏

存储过程

1 篇文章 0 订阅

订阅专栏


/**
                    .::::.
                  .::::::::.
                 :::::::::::    佛主保佑、永无Bug
             ..:::::::::::'
           '::::::::::::'
             .::::::::::
        '::::::::::::::..
             ..::::::::::::.
           ``::::::::::::::::
            ::::``:::::::::'        .:::.
           ::::'   ':::::'       .::::::::.
         .::::'      ::::     .:::::::'::::.
        .:::'       :::::  .:::::::::' ':::::.
       .::'        :::::.:::::::::'      ':::::.
      .::'         ::::::::::::::'         ``::::.
  ...:::           ::::::::::::'              ``::.
 ````':.          ':::::::::'                  ::::..
                    '.:::::'                    ':'````..
*/
/**
 *
 * @Authror 万物皆导
 * @Date ${DATE} ${TIME}
 */

毋庸置疑，这肯定是一个悲伤的故事，我这边有一个需求，我要对一些记录和日志表进行迁移备份，介于当下条件，我们没有使用 MaxCompute 进行备份，也没有通过主从数据库关系或者 DTS 等等其它技术进行优雅处理，所以我们当下使用一个比较简单的处理方式，就是通过定时任务进行唤起存储过程进行备份操作。

根据以上需求，显而易见，存储过程里面的内容便是核心，其实相对来说也比较简单，无非就是分三步走，如下：

对源表表结构进行复制
备份指定时间的数据
删除源表里面的已经备份的数据
首先我这边会创建一个备份配置表，如下：


-- 创建备份配置表

CREATE TABLE `back_up_base` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `source_database` varchar(100) NOT NULL COMMENT '源库',
  `source_table` varchar(100) NOT NULL COMMENT '源表',
  `destination_database` varchar(100) NOT NULL COMMENT '目标库',
  `destination_table` varchar(100) NOT NULL COMMENT '目标表',
  `days` int(11) unsigned NOT NULL DEFAULT '15' COMMENT '保留天数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

接下来就是写存储过程了，首先看看我第一版写的，如下：


DELIMITER $$
# 备份存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `back_up_record`()
BEGIN	
    # 声明变量信息
    DECLARE source_database VARCHAR(100) DEFAULT '';
    DECLARE source_table VARCHAR(100) DEFAULT '';
    DECLARE destination_database VARCHAR(100) DEFAULT '';
    DECLARE destination_table VARCHAR(100) DEFAULT '';
    DECLARE days INT DEFAULT 15;
    DECLARE done int DEFAULT FALSE;
		
		

    # 获取需要备份的数据库信息
    DECLARE cur_back_up CURSOR FOR SELECT source_database,source_table,destination_database,destination_table,days FROM `backup`.back_up_base;
	# 定义循环结束标志
   	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true;


    # 打开游标
    OPEN cur_back_up;

			# 开始遍历
			read_loop: LOOP
				FETCH cur_back_up INTO source_database,source_table,destination_database,destination_table,days;
				IF done THEN LEAVE read_loop; 
				END IF; 
				
				# 创建表
				# 设置执行语句
				SET @create_table = CONCAT('CREATE TABLE IF NOT EXISTS `',destination_database,'`.`',destination_table,'` (LIKE `',source_database,'`.`',source_table,'`)');
	     		# 预处理动态 sql
				PREPARE stmt_create_table FROM @create_table;
				# 执行 sql
				EXECUTE stmt_create_table;
				# 释放预处理
				DEALLOCATE PREPARE stmt_create_table;

				# 备份数据
                # 设置执行语句
                SET @back_up_data = CONCAT('INSERT INTO `',destination_database,'`.`',destination_table,'` SELECT * FROM `',source_database,'`.`',source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',days,' DAY)');
				# 预处理动态 sql
                PREPARE stmt_back_up_data FROM @back_up_data;
                # 执行 sql
                EXECUTE stmt_back_up_data;
                # 释放预处理
                DEALLOCATE PREPARE stmt_back_up_data;


                # 删除数据
                # 设置执行语句
                SET @delete_data = CONCAT('DELETE FROM `',source_database,'`.`',source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',days,' DAY)');
				# 预处理动态 sql
                PREPARE stmt_delete_data FROM @delete_data;
                # 执行 sql
                EXECUTE stmt_delete_data;
                # 释放预处理
                DEALLOCATE PREPARE stmt_delete_data;

			END LOOP read_loop;
    # 关闭游标
    CLOSE cur_back_up;

END $$

DELIMITER ;

咋一看，赶脚没啥问题哈，一顿操作猛如虎，就是老执行报错，因为我使用的是 navicat,所以不怎么汇调试，阿导只有三年前使用 oracle 才用过存储过程并加以调试。那我只能注释一行行代码来排查问题，最终发现问题是遍历的时候，FETCH 并没有获取到元数据，这是一个很苦逼的事。

郁闷了一天，先使用 java 动态列出所有的需要备份的表，然后进行执行，临时撑个场子。

阿导绝不能认输，我一定的找到引起这个问题的原因，毕竟学无止境，尤其是咱们这行，必须要有打破砂锅问到底的精神，做到不抛弃不放弃的精神。

通过阅读大量前辈的经验，我发现存储过程中定义的变量名称不能和游标体里面的变量一样，否则造成混淆，导致无法获取到值的情况，者是真尴尬，毕竟按照我们程序员思路，这个应该在编译环节就抛出的异常，所以阿导遇到这个问题是真郁闷，也怪阿导学艺不精导致的，希望我这边记录下来并分享给需要帮助的人，少走一些弯路。

修改后的存储过程如下：


DELIMITER $$

# 创建存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `back_up_record`()
BEGIN
		
    # 声明变量信息
    DECLARE cur_source_database VARCHAR(100) DEFAULT '';
    DECLARE cur_source_table VARCHAR(100) DEFAULT '';
    DECLARE cur_destination_database VARCHAR(100) DEFAULT '';
    DECLARE cur_destination_table VARCHAR(100) DEFAULT '';
    DECLARE cur_days INT DEFAULT 15;
    DECLARE done int DEFAULT FALSE;
		
		

    # 获取需要备份的数据库信息
    DECLARE cur_back_up CURSOR FOR SELECT source_database,source_table,destination_database,destination_table,days FROM `backup`.back_up_base;
	  # 定义循环结束标志
   	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true;


    # 打开游标
    OPEN cur_back_up;

			# 开始遍历
			read_loop: LOOP
				FETCH cur_back_up INTO cur_source_database,cur_source_table,cur_destination_database,cur_destination_table,cur_days;
				IF done THEN LEAVE read_loop; 
				END IF; 
				
				# 创建表
				# 设置执行语句
				SET @create_table = CONCAT('CREATE TABLE IF NOT EXISTS `',cur_destination_database,'`.`',cur_destination_table,'` (LIKE `',cur_source_database,'`.`',cur_source_table,'`)');
				# 预处理动态 sql
				PREPARE stmt_create_table FROM @create_table;
				# 执行 sql
				EXECUTE stmt_create_table;
				# 释放预处理
				DEALLOCATE PREPARE stmt_create_table;

				# 备份数据
        		# 设置执行语句
        		SET @back_up_data = CONCAT('INSERT INTO `',cur_destination_database,'`.`',cur_destination_table,'` SELECT * FROM `',cur_source_database,'`.`',cur_source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',cur_days,' DAY)');
				# 预处理动态 sql
        		PREPARE stmt_back_up_data FROM @back_up_data;
        		# 执行 sql
        		EXECUTE stmt_back_up_data;
        		# 释放预处理
       			DEALLOCATE PREPARE stmt_back_up_data;


        		# 删除数据
        		# 设置执行语句
        		SET @delete_data = CONCAT('DELETE FROM `',cur_source_database,'`.`',cur_source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',cur_days,' DAY)');
				# 预处理动态 sql
        		PREPARE stmt_delete_data FROM @delete_data;
        		# 执行 sql
        		EXECUTE stmt_delete_data;
       			# 释放预处理
        		DEALLOCATE PREPARE stmt_delete_data;


			END LOOP read_loop;
    # 关闭游标
    CLOSE cur_back_up;

END $$

DELIMITER ;

终于到了最后一步，定时开启定时任务就行了，如下：


CREATE EVENT IF NOT EXISTS backup_record_event   
ON SCHEDULE EVERY 1 DAY DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR) 
ON COMPLETION PRESERVE ENABLE   

-- 这边需要更改库，目前设置每天凌晨1点跑数据
DO CALL back_up_record();

至此已结束。

需要云服务器的不要错过优惠

阿里云低价购买云服务，值得一看

万物皆导_

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
Mysql 存储过程遍历无法获取值的坑

毋庸置疑，这肯定是一个悲伤的故事，我这边有一个需求，我要对一些记录和日志表进行迁移备份，介于当下条件，我们没有使用 MaxCompute 进行备份，也没有通过主从数据库关系或者 DTS 等等其它技术进行优雅处理，所以我们当下使用一个比较简单的处理方式，就是通过定时任务进行唤起存储过程进行备份操作。根据以上需求，显而易见，存储过程里面的内容便是核心，其实相对来说也比较简单，无非就是分三步走，如下：...
复制链接

扫一扫

专栏目录