程式員：你如何寫可重覆執行的SQL語句？

来源:https://www.cnblogs.com/snidget/archive/2023/09/05/17680658.html
-Advertisement-
Play Games

![file](https://img2023.cnblogs.com/other/268922/202309/268922-20230905194637565-1884920101.png) 上圖的意思： 百戰百勝，屢試不爽。 # 故事 ![file](https://img2023.cnblog ...


file

上圖的意思： 百戰百勝，屢試不爽。

故事

file

程式員小張： 剛畢業，參加工作1年左右，日常工作是CRUD

file

架構師老李： 多個大型項目經驗，精通各種開發架構屠龍寶術；

小張註意到，在實際的項目開發場景中，很多開發人員只關註編寫SQL腳本來滿足功能需求，而忽略了腳本的可重覆執行性。

這就意味著，如果腳本中的某個部分執行失敗，運維人員就必須從頭提供一個新的腳本，這對運維團隊和開發人員來說是一個挑戰。

因此，小張決定研究如何編寫基於MySQL的可以重覆執行的SQL腳本，以提高開發效率和簡化運維流程。

他向公司的架構師老李咨詢了這個問題。老李是一位經驗豐富的架構師，

他在多個大型項目中積累了許多寶貴的經驗，精通各種開發架構屠龍寶術。

老李聽了小張的問題後，笑了笑並開始給予指導。他向小張解釋瞭如何編寫一個具有可重覆執行性的SQL腳本，並分享了以下幾個關鍵點：

a.使用事務：事務是一組SQL語句的邏輯單元，可以保證這組語句要麼全部執行成功，要麼全部回滾。

   通過使用事務，可以確保腳本的所有修改操作要麼完整地執行，要麼不執行。

b.使用條件檢查：在每個需要修改數據的語句之前，添加條件檢查以確保只有當數據不存在或滿足特定條件時才進行修改。

   這樣可以避免重覆插入相同的數據，或者執行不必要的更新操作。

c.錯誤處理：在編寫腳本時，考慮到可能出現的錯誤情況，並提供適當的錯誤處理機制。例如，使用IF...ELSE語句來處理特定條件下的執行邏輯。

d.使用存儲過程：如果腳本非常複雜，包含多個步驟和業務邏輯，可以考慮將它們封裝為存儲過程。這樣可以更好地組織和管理代碼，並提高腳本的可讀性和維護性。

小張聽得津津有味，他開始將老李的建議付諸實踐。他仔細研究每個SQL語句，根據老李的指導進行修改和優化。

他使用了事務來包裹整個腳本，添加了條件檢查來避免重覆插入數據，並實現了錯誤處理機制以應對異常情況。

背景

所以開發提供給到運維的SQL腳本有一定基本要求：

1.能重覆執行；

2.不出錯，（不報錯，邏輯正確）；

如果腳本不可重覆執行，則運維無法自動化，會反過來要求後端開發人員給出適配當前環境的新的SQL腳本，增加了運維和溝通成本。

那麼怎麼寫可重覆執行的SQL腳本呢？

分成4個場景，來介紹舉例。

file

1 創建表

create table if not exists nginx_config (
    id varchar(36) not null default '' comment 'UUID',
	namespace varchar(255) not null default '' comment '環境命名空間',
    config_content text comment "nginx http塊配置",
    content_md5 varchar(64) not null default '' comment '配置內容的MD5值',
    manipulator varchar(64) not null default '' comment '操作者',
    description varchar(512) not null default '' comment '描述',
	gmt_created bigint unsigned not null default 0 comment '創建時間',
    primary key(id)
)ENGINE=InnoDB comment 'nginx配置表' ;

刪除表在生產環境是禁止的。

備份方式修改表名

修改表名： 先創建新表，再copy歷史數據進去，不允許刪除表；

DELIMITER //
drop procedure if exists modify_table_name;
CREATE PROCEDURE modify_table_name(
  IN table_name VARCHAR(255),
  IN new_name VARCHAR(255)
)
BEGIN
  DECLARE database_name VARCHAR(255);
  DECLARE table_exists INT DEFAULT 0;
  DECLARE new_table_exists INT DEFAULT 0;
  SELECT DATABASE() INTO database_name;
  set @db_table_name=concat(database_name,'/',table_name);
  select count(t1.TABLE_ID) INTO table_exists  from information_schema.INNODB_TABLES t1 where t1.NAME=@db_table_name ;
  set @db_table_name_new=concat(database_name,'/',new_name);
  select count(t1.TABLE_ID) INTO new_table_exists  from information_schema.INNODB_TABLES t1 where t1.NAME=@db_table_name_new ;

  IF table_exists = 1 AND new_table_exists = 0 THEN
    SET @query = CONCAT('create table ',new_name,' like ',table_name);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @query = CONCAT('insert into ', new_name, ' select * from ',table_name);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT 'table name modify successfully.' AS result ,@db_table_name,@db_table_name_new,table_exists,new_table_exists;
  ELSE
    SELECT 'table name not exists or new_name already exists.' AS result,@db_table_name,@db_table_name_new,table_exists,new_table_exists;
  END IF;

END //

DELIMITER ;

測試腳本：

create table user(id bigint auto_increment primary key ,name varchar(30),age tinyint)comment 'user表';

insert into user(id, name, age) VALUES  (1,'a',1),(2,'b',2),(3,'c',3);


call modify_table_name('user','user1');

select * from user1;


call modify_table_name('user','user2');

select * from user2;

測試結果符合預期。

新增修改刪除欄位

drop procedure if exists modify_table_field;
CREATE PROCEDURE modify_table_field(IN tableName VARCHAR(50), IN fieldName VARCHAR(50), IN fieldAction VARCHAR(10), IN fieldType VARCHAR(255))
BEGIN
    IF fieldAction = 'add' THEN
        IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_name = tableName AND column_name = fieldName) THEN
            SET @query = CONCAT('ALTER TABLE ', tableName, ' ADD COLUMN ', fieldName, ' ', fieldType);
            PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT 'Field added successfully.' AS result;
        ELSE
            SELECT 'Field already exists.' AS result;
        END IF;
    ELSEIF fieldAction = 'modify' THEN
        IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = tableName AND column_name = fieldName) THEN
            SET @query = CONCAT('ALTER TABLE ', tableName, ' CHANGE COLUMN ', fieldName, ' ', fieldName, ' ', fieldType);
            select @query;
            PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT 'Field modified successfully.' AS result;
        ELSE
            SELECT 'Field does not exist or has the same name.' AS result;
        END IF;
    ELSEIF fieldAction = 'delete' THEN
        IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = tableName AND column_name = fieldName) THEN
            SET @query = CONCAT('ALTER TABLE ', tableName, ' DROP COLUMN ', fieldName);
            PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT 'Field deleted successfully.' AS result;
        ELSE
            SELECT 'Field does not exist.' AS result;
        END IF;
    ELSE
        SELECT 'Invalid field action.' AS result;
    END IF;
END;

測試腳本

create table if not exists sys_agent
(
    agent_id          bigint                             not null comment '客服唯一id' primary key,
    agent_name        varchar(64)                        null comment '客服名稱',
    agent_type        varchar(30)                        null comment '客服類型(場地客服、直聘客服)',
    district          varchar(30)                        null comment '地區',
    service_language  varchar(30)                        null comment '服務語種',
    agent_description varchar(500)                       null comment '客戶描述',
    status            tinyint(1)                         null comment '狀態(0=無效,1=有效)，預設為1',
    del_flag          tinyint(1)                         null comment '是否刪除(0=false,1=true)',
    user_id           bigint                             null comment '用戶id(關聯的用戶信息)',
    time_zone         varchar(50)                        null comment '時區',
    create_by         varchar(50)                        null comment '創建者',
    create_time       datetime default CURRENT_TIMESTAMP null comment '創建時間',
    update_by         varchar(50)                        null comment '修改者',
    update_time       datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '修改時間'
) comment '客服管理';



CALL modify_table_field('sys_agent', 'sex', 'add', 'tinyint not null comment ''性別''');
CALL modify_table_field('sys_agent', 'sex2', 'add', 'tinyint not null comment ''性別''');


CALL modify_table_field('sys_agent', 'sex', 'modify', 'int not null comment ''性別''');


CALL modify_table_field('sys_agent', 'sex', 'delete', '');
CALL modify_table_field('sys_agent', 'sex2', 'delete', '');

測試結果符合預期。

新增修改刪除索引

一般放在建表語句中，80%的情況；

如果是項目後期增加索引，進行調優，可以參考欄位，寫一個存儲過程支持索引的新增可以重覆執行；

DELIMITER //
drop procedure if exists modify_table_index;
CREATE PROCEDURE modify_table_index(
  IN table_name VARCHAR(255),
  IN index_name VARCHAR(255),
  IN index_action ENUM('add', 'modify', 'delete'),
  IN index_columns VARCHAR(255)
)
BEGIN
  DECLARE database_name VARCHAR(255);
  DECLARE index_exists INT DEFAULT 0;
  DECLARE index_exists_action INT DEFAULT 0;

  -- 獲取當前資料庫名
  SELECT DATABASE() INTO database_name;

  set @db_table_name=concat(database_name,'/',table_name);
  -- 檢查索引是否存在
  select count(t2.INDEX_ID) INTO index_exists  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID
  where t1.NAME=@db_table_name and t2.NAME=index_name;

  set index_exists_action=index_exists;

  IF index_action = 'add' THEN
    -- 添加索引
    IF index_exists < 1 THEN
      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      select count(t2.INDEX_ID) INTO index_exists_action  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
      SELECT 'Index added successfully.' AS result ,database_name,index_exists,@db_table_name,index_exists_action;
    ELSE
      SELECT 'Index already exists.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
    END IF;

  ELSEIF index_action = 'modify' THEN
    -- 修改索引（先刪除後添加）
    IF index_exists > 0 THEN
      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` DROP INDEX `', index_name, '`');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      select count(t2.INDEX_ID) INTO index_exists_action  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
      SELECT 'Index modified successfully.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
    ELSE
      SELECT 'Index does not exist. create' AS result,database_name,index_exists,@db_table_name,index_exists_action;

      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      select count(t2.INDEX_ID) INTO index_exists_action  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
      SELECT 'Index added successfully.' AS result ,database_name,index_exists,@db_table_name,index_exists_action;
    END IF;

  ELSEIF index_action = 'delete' THEN
    -- 刪除索引
    IF index_exists > 0 THEN
      SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` DROP INDEX `', index_name, '`');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      select count(t2.INDEX_ID) INTO index_exists_action  from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
      SELECT 'Index deleted successfully.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
    ELSE
      SELECT 'Index does not exist.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
    END IF;

  ELSE
    SELECT 'Invalid index action.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
  END IF;

END //

DELIMITER ;

測試腳本

create table if not exists sys_agent
(
    agent_id          bigint                             not null comment '客服唯一id'
        primary key,
    agent_name        varchar(64)                        null comment '客服名稱',
    agent_type        varchar(30)                        null comment '客服類型(場地客服、直聘客服)',
    district          varchar(30)                        null comment '地區',
    service_language  varchar(30)                        null comment '服務語種',
    agent_description varchar(500)                       null comment '客戶描述',
    status            tinyint(1)                         null comment '狀態(0=無效,1=有效)，預設為1',
    del_flag          tinyint(1)                         null comment '是否刪除(0=false,1=true)',
    user_id           bigint                             null comment '用戶id(關聯的用戶信息)',
    time_zone         varchar(50)                        null comment '時區',
    create_by         varchar(50)                        null comment '創建者',
    create_time       datetime default CURRENT_TIMESTAMP null comment '創建時間',
    update_by         varchar(50)                        null comment '修改者',
    update_time       datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '修改時間'
)comment '客服管理';


CALL modify_table_index('sys_agent', 'ix_agentName', 'add', 'agent_name,agent_type');
CALL modify_table_index('sys_agent', 'ix_agentName', 'delete', '');
CALL modify_table_index('sys_agent', 'ix_agentName', 'modify', 'agent_name,agent_type');

新增數據

replace into語句 按照主鍵或者唯一值，存在則先刪除再插入，不存在則直接插入；

註意： 一定要寫欄位名稱。

REPLACE INTO route_config (route_id, route_order, route_uri, route_filters, route_predicates, route_metadata, memo, created, updated, deleted) VALUES ('app-metadata-runtime', 1, 'lb://app-metadata-runtime', '[{"name":"StripPrefix","args":{"parts":"2"}}]', '[{"name":"Path","args":{"pattern":"/api/mr/**"}}]', '{}', '雲樞服務app-metadata-runtime', '2020-07-31 21:44:11', '2020-09-07 20:24:13', 0);

小結

按照不同的場景寫了對應的存儲過程，使得修改欄位，修改索引，修改表，插入數據可以重覆執行。

如果有使用問題或者優化建議，歡迎提出來。還原跟我交流 ;

原創不易，關註誠可貴，轉發價更高！轉載請註明出處，讓我們互通有無，共同進步，歡迎溝通交流。

no pays,no gains!
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 【NKeditor】富文本編輯器上傳圖片
    目的： 使用NKeditor富文本編輯器上傳圖片，同時上傳到七牛雲存儲上。後端語言使用ThinkPHP。 效果 實現方法： 1、下載NKeditor插件庫 下載地址：NKeditor: NKedtior是一款優秀的輕量級Web編輯器，基於 Kindedior 二次開發 裡面的文檔demo寫的比較詳細 ...
  • 小白最近比較閑，於是整理了一些日常使用的VUE組件
    1.VUE中給Dom元素動態添加樣式 VUE中，給 Dom 元素動態添加樣式。 比如判斷通過頁面傳遞過來的值和env文件中配置的值是否一致，來動態添加元素的類，同時類的樣式在 Style 中已經寫好。 此時動態類名需要在 Dom 元素載入完成前添加上，否則樣式可能添加不上。 這種情況下可以在 com ...
  • 如何再vue項目中使用cdn（以使用天地圖得cdn獲取當前位置為例）
    # 一、瞭解天地圖 http://lbs.tianditu.gov.cn/api/js4.0/examples.html 在其中可以瞭解天地圖的基本使用教程 但其中的教程均為h5引入cdn的方式 以h5定位為例來改成vue項目 源碼: ```html5 天地圖－地圖API－範例－H5定位 本示例演示 ...
  • 小白弄明白了 unix 時間戳的轉換問題
    小白對於將 unix 時間戳轉換為日期時間和使用日期時間轉換為 unix 時間戳，在項目中見到過很多，每次使用時不是用現有的方法轉換就是網上搜索方法。 小白見過各種轉換方式覺得moment庫很是方便，但是用法較多，所以小白決定整理一下。以後再遇到時間日期轉換可能手寫代碼而省去翻看資料的時間。 vue ...
  • Spring Boot中自動裝配機制的原理
    1.自動裝配，簡單來說就是自動把第三方組件的Bean裝載到Spring IOC容器裡面，不需要開發人員再去寫Bean的裝配配置， 2.在Spring Boot應用裡面，只需要在啟動類加上@SpringBootApplication註解就可以實現自動裝配。 ...
  • 開發軟技能——Git Commit規範
    提交代碼是程式員們每天的工作日常，今天敬姐給大家分享一個好的編程習慣，就是關於Git Commit規範。 ## 效果預覽 ``` (): ``` 提交之後的效果如下： ![img](https://img2023.cnblogs.com/blog/37001/202309/37001-2023090 ...
  • Bridge Pattern
    # What is Bridge Pattern 橋接模式（Bridge Pattern），旨在將抽象部分和實現部分解耦，使它們可以獨立地變化。該模式通過將抽象和實現分離，使它們可以獨立地進行擴展和修改，同時通過橋接（Bridge）將它們連接起來。 將一個事物原本耦合在一起的東西，通過定義成抽象和實 ...
  • 《C++併發編程實戰》讀書筆記(4)：原子變數
    ## 1、標準原子類型 標準原子類型的定義位於頭文件``內。原子操作的關鍵用途是取代需要互斥的同步方式，但假設原子操作本身也在內部使用了互斥，就很可能無法達到期望的性能提升。有三種方法來判斷一個原子類型是否屬於無鎖數據結構： - 所有標準原子類型（`std::atomic_flag`除外，因為它必須 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類