批量更新不同表的数据

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

当时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);

查看结果

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。

---

编辑：广州明生堂生物科技有限公司

标签：批量,语句,类型,代码,业务