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


/**
                    .::::.
                  .::::::::.
                 :::::::::::    佛主保佑、永无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 $$
# 备份存储过程
MySQL,由于其语法限制,原生并没有数组数据结构,但你可以通过用户自定义变量(例如VARCHAR类型的大字符串)模拟数组功能。如果你想在存储过程遍历一个类似数组的数据,可以采用以下步骤: 1. 定义一个大字符串,用逗号分隔每个元素。例如: ```sql DECLARE @array_string VARCHAR(4000) DEFAULT 'element1, element2, element3'; ``` 2. 将整个字符串分解成一个临时表,然后逐个处理每个元素。可以使用FIND_IN_SET()函数配合SUBSTRING_INDEX()来做到这一点。比如: ```sql SET @array = (SELECT GROUP_CONCAT(TMP.element SEPARATOR ', ') FROM ( SELECT SUBSTRING_INDEX(@array_string, ',', n.n) as element FROM (SELECT a.N FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 LIMIT 100) AS a(N) ) x(n) JOIN (SELECT @row := @row + 1 as row) r ON r.row = n.n ) TMP WHERE TMP.element != ''); ``` 这里,`GROUP_CONCAT()`将拆分后的元素合并回一个字符串,`SUBSTRING_INDEX()`用于获取指定位置的元素,`LIMIT 100`是为了避免一次性处理过长的字符串。 3. 遍历这个临时表`TMP`,对每个元素执行你需要的操作。例如: ```sql WHILE @array != '' DO SET @current_element = SUBSTRING_INDEX(@array, ',', 1); -- 执行与当前元素相关的操作 SELECT * FROM your_table WHERE condition LIKE CONCAT('%', @current_element, '%'); SET @array = SUBSTRING(@array, LENGTH(@current_element) + 2); END WHILE; ``` 注意,这种方法效率不高,特别是对于大型数组,因为它涉及到多次字符串分割。如果性能很重要,你可能需要考虑其他数据库系统,如PostgreSQL,它们提供了更好的数组处理能力。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值