快捷導(dǎo)航

MySQL實(shí)現(xiàn)批量更新不同表中的數(shù)據(jù)

更新時(shí)間：2022年05月07日 15:32:11 作者：風(fēng)水月

這篇文章主要介紹了MySQL實(shí)現(xiàn)批量更新不同表中的數(shù)據(jù)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

批量更新不同表的數(shù)據(jù)

今天翻到以前寫(xiě)的批量更新表中的數(shù)據(jù)的存儲(chǔ)過(guò)程，故在此做一下記錄。

當(dāng)時(shí)MySQL中的表名具有如下特征，即根據(jù)需求將業(yè)務(wù)表類型分為了公有、私有和臨時(shí)三種類型，即不同的業(yè)務(wù)對(duì)應(yīng)三張表，而所做的是區(qū)分出是什么類型（公有、私有、臨時(shí)）的業(yè)務(wù)表對(duì)數(shù)據(jù)的固定字段做統(tǒng)一規(guī)律的處理。

下面為當(dāng)時(shí)所編寫(xiě)的存儲(chǔ)過(guò)程

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庫(kù)中以'temp_test_p_'開(kāi)頭的表
  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;
    -- 連接字符串函數(shù)
    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的內(nèi)容為

1.查詢表名

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

2.執(zhí)行拼接的字符串SQL

PREPARE statement_name FROM sql_text /*定義*/?
EXECUTE statement_name [USING variable [,variable...]] /*執(zhí)行預(yù)處理語(yǔ)句*/?
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;

批量更新語(yǔ)句（UPDATE）

使用UPDATE語(yǔ)句實(shí)現(xiàn)批量修改

示例

下面創(chuàng)建一個(gè)名為‘bhl_tes’的數(shù)據(jù)庫(kù)，并創(chuàng)建名為‘test_user’的表，字段分別為‘id’，‘age’，‘name’，’sex‘。

創(chuàng)建數(shù)據(jù)庫(kù)‘bhl_tes’

代碼

CREATE DATABASE IF NOT EXISTS bhl_test;

查看結(jié)果

創(chuàng)建表‘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;

查看結(jié)果

批量插入記錄

INSERT INTO test_user 
(name, age, sex)
VALUES 
('張三', 18, '男'),
('趙四', 17, '女'),
('劉五', 16, '男'),
('周七', 19, '女');

查看結(jié)果

批量修改記錄

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