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

更新時間：2022年05月07日 15:32:11 作者：風水月

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

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

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

當時MySQL中的表名具有如下特征，即根據(jù)需求將業(yè)務表類型分為了公有、私有和臨時三種類型，即不同的業(yè)務對應三張表，而所做的是區(qū)分出是什么類型（公有、私有、臨時）的業(yè)務表對數(shù)據(jù)的固定字段做統(tǒng)一規(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庫中以'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;
    -- 連接字符串函數(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í)行預處理語句*/?
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語句實現(xiàn)批量修改

示例

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

創(chuàng)建數(shù)據(jù)庫‘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);

查看結(jié)果

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

mysql mysqldump數(shù)據(jù)備份和增量備份
本篇文章主要講如何使用shell實現(xiàn)mysql全量，增量備份，還可以按時間備份。
2013-10-10
如何設計高效合理的MySQL查詢語句
合理的MySQL查詢語句可以讓我們的MySQL數(shù)據(jù)庫效率更高，那么如何設計高效合理的查詢語句就成為了擺在我們面前的問題。
2015-08-08
Mysql如何查詢近30天活躍用戶數(shù)
這篇文章主要介紹了Mysql查詢近30天活躍用戶數(shù),本文通過實例代碼給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧
2024-08-08
解析在MySQL里創(chuàng)建外鍵時ERROR 1005的解決辦法
本篇文章是對在MySQL里創(chuàng)建外鍵時ERROR 1005的解決辦法進行了詳細的分析介紹，需要的朋友參考下
2013-06-06
MySQL的聯(lián)表查詢實現(xiàn)
數(shù)據(jù)通常分布在多個表中,為了獲取全面的信息,需要進行聯(lián)表查詢,本文主要介紹了MySQL的聯(lián)表查詢實現(xiàn),具有一定的參考價值,感興趣的可以了解一下
2024-08-08
CentOS 7.2 Yum編譯安裝MySQL 5.6
這篇文章主要為大家介紹了CentOS 7.2 Yum編譯安裝MySQL 5.6詳細代碼,具有一定的參考價值，感興趣的小伙伴們可以參考一下
2016-11-11
Mysql數(shù)據(jù)庫的日志管理、備份與回復詳細圖文教程
備份的主要目的是災難恢復,備份還可以測試應用、回滾數(shù)據(jù)修改、查詢歷史數(shù)據(jù)、審計等,這篇文章主要給大家介紹了關于Mysql數(shù)據(jù)庫的日志管理、備份與回復的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
2024-08-08
MySQL視圖和索引專篇精講
這篇文章主要介紹了MySQL的視圖和索引用法與區(qū)別詳解，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2022-03-03
MySQL如何使用使用Xtrabackup進行備份和恢復
Xtrabackup是由Percona開發(fā)的一個開源軟件,可實現(xiàn)對InnoDB的數(shù)據(jù)備份,支持在線熱備份(備份時不影響數(shù)據(jù)讀寫)。本文講解如何使用該工具進行備份和恢復
2021-06-06
windows下重啟mysql的方法
這篇文章主要介紹了windows下重啟mysql的方法,需要的朋友可以參考下
2014-12-12