批量更新不同表的数据
今天翻到以前写的批量更新表中的数据的存储过程，故在此做一下记录。
当时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);
查看结果
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文地址：https://blog.csdn.net/fengshuiyue/article/details/44522033
