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

吾爱主题阅读：140 2024-04-01 23:25:20 评论：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;

批量更新语句（UPDATE）

使用UPDATE语句实现批量修改

示例

下面创建一个名为‘bhl_tes’的数据库，并创建名为‘test_user’的表，字段分别为‘id’，‘age’，‘name’，’sex‘。

创建数据库‘bhl_tes’

代码

CREATE DATABASE IF NOT EXISTS bhl_test;

查看结果

创建表‘test_user’

代码

CREATE TABLE IF NOT EXISTS `test_user`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(255) NOT NULL,
   `age` INT(11) NOT NULL,
   `sex` VARCHAR(16),
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看结果

批量插入记录

INSERT INTO test_user 
(name, age, sex)
VALUES 
("张三", 18, "男"),
("赵四", 17, "女"),
("刘五", 16, "男"),
("周七", 19, "女");

查看结果

批量修改记录

UPDATE test_user SET 
name = CASE id 
WHEN 1 THEN "张三" 
WHEN 2 THEN "李四" 
WHEN 3 THEN "王五" 
WHEN 4 THEN "小六" 
END,
age = CASE id 
WHEN 1 THEN 7 
WHEN 2 THEN 8 
WHEN 3 THEN 9 
WHEN 4 THEN 14 
END,
sex = CASE id 
WHEN 1 THEN "男" 
WHEN 2 THEN "男" 
WHEN 3 THEN "男" 
WHEN 4 THEN "男" 
END
WHERE id IN (1,2,3,4);