MySQL批量更新不同表中的数据

深藏阁楼爱情的钟 2022-08-07 06:58 179阅读 0赞

今天翻到以前写的批量更新表中的数据的存储过程，故在此做一下记录。

当时MySQL中的表名具有如下特征，即根据需求将业务表类型分为了公有、私有和临时三种类型，即不同的业务对应三张表，而所做的是区分出是什么类型（公有、私有、临时）的业务表对数据的固定字段做统一规律的处理。

下面为当时所编写的存储过程：

BEGIN
      DECLARE done             INT;
      DECLARE v_table_name     VARCHAR(100);
      DECLARE v_disable        VARCHAR(100);
      DECLARE v_disable_temp   VARCHAR(100); -- 存放最终删除sql 
      DECLARE v_table_pre      VARCHAR(100);
      DECLARE v_table_sub      VARCHAR(200);
      DECLARE v_disable_temp_2 VARCHAR(100);
      -- 查询testkaifa库中以'temp_test_p_'开头的表
      DECLARE cursor_table_gis CURSOR FOR SELECT DISTINCT table_name tableName
                                          FROM
                                            information_schema.columns
                                          WHERE
                                            table_schema = 'testkaifa'
                                            AND table_name LIKE '%temp_test_p_%';
    
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
      SELECT @done;
    
      OPEN cursor_table_gis;
    
    cursor_loop:
      LOOP
        FETCH cursor_table_gis INTO v_table_name;
        IF done = 1 THEN
          LEAVE cursor_loop;
        END IF;
        -- 连接字符串函数
        SET @v_disable = concat_ws(' ', 'update ', v_table_name, 'set is_valid=false where expire_time>now();');
        SELECT @v_disable;
        PREPARE sqlstr FROM @v_disable;
        EXECUTE sqlstr;
        DEALLOCATE PREPARE sqlstr;
    
        SELECT substring_index(v_table_name, '_', 1)
        INTO
          v_table_pre;
        -- IF v_table_pre = 'temp' THEN
        SELECT reverse(left(reverse(v_table_name), instr(reverse(v_table_name), '_')))
        INTO
          v_table_sub;
        SET @v_disable_temp = concat_ws(' ', 'update ', v_table_name, 'set is_valid=false where (expire_time-now())> (select value_data from ', concat('platform_params_p', v_table_sub), 'where param_key=\'tempDismissInterval\');');
        SELECT @v_disable_temp;
        PREPARE sqlstr2 FROM @v_disable_temp;
        EXECUTE sqlstr2;
        DEALLOCATE PREPARE sqlstr2;
        --  END IF;
    
        SET @v_disable_temp_2 = concat_ws(' ', 'update ', v_table_name, 'set is_valid=false where (test_id in(select test_id from ', concat('temp_test_user_p', v_table_sub), ' where (max(latest_act_time )-now())> (select value_data from ', concat('platform_params_p', v_table_sub), 'where param_key=\'tempDismissInterval\'));');
        SELECT @v_disable_temp_2;
        PREPARE sqlstr2 FROM @v_disable_temp;
        EXECUTE sqlstr2;
        DEALLOCATE PREPARE sqlstr2;
    
      END LOOP cursor_loop;
    
      CLOSE cursor_table_gis;
    
      COMMIT;
    -- 
    END

本代码涉及到的MySQL的内容为：

1.查询表名

SELECT DISTINCT table_name tableName
                                          FROM
                                            information_schema.columns
                                          WHERE
                                            table_schema = 'testkaifa'
                                            AND table_name LIKE '%temp_test_p_%';

2.执行拼接的字符串SQL

PREPARE statement_name FROM sql_text /*定义*/ 
    EXECUTE statement_name [USING variable [,variable...]] /*执行预处理语句*/ 
    DEALLOCATE PREPARE statement_name /*删除定义*/

例如：

SET @v_disable_temp = concat_ws(' ', 'update ', v_table_name, 'set is_valid=false where (expire_time-now())> (select value_data from ', concat('platform_params_p', v_table_sub), 'where param_key=\'tempDismissInterval\');');
        SELECT @v_disable_temp;
        PREPARE sqlstr2 FROM @v_disable_temp;
        EXECUTE sqlstr2;
        DEALLOCATE PREPARE sqlstr2;