程序員：你如何寫可重復執(zhí)行的SQL語句？

file

上圖的意思： 百戰(zhàn)百勝，屢試不爽。

故事

file

程序員小張： 剛畢業(yè)，參加工作1年左右，日常工作是CRUD

file

架構(gòu)師老李： 多個大型項目經(jīng)驗，精通各種開發(fā)架構(gòu)屠龍寶術(shù)；

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

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

因此，小張決定研究如何編寫基于MySQL的可以重復執(zhí)行的SQL腳本，以提高開發(fā)效率和簡化運維流程。

他向公司的架構(gòu)師老李咨詢了這個問題。老李是一位經(jīng)驗豐富的架構(gòu)師，

他在多個大型項目中積累了許多寶貴的經(jīng)驗，精通各種開發(fā)架構(gòu)屠龍寶術(shù)。

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

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

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

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

   這樣可以避免重復插入相同的數(shù)據(jù)，或者執(zhí)行不必要的更新操作。

c.錯誤處理：在編寫腳本時，考慮到可能出現(xiàn)的錯誤情況，并提供適當?shù)腻e誤處理機制。例如，使用IF...ELSE語句來處理特定條件下的執(zhí)行邏輯。

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

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

他使用了事務來包裹整個腳本，添加了條件檢查來避免重復插入數(shù)據(jù)，并實現(xiàn)了錯誤處理機制以應對異常情況。

背景

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

1.能重復執(zhí)行；

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

如果腳本不可重復執(zhí)行，則運維無法自動化，會反過來要求后端開發(fā)人員給出適配當前環(huán)境的新的SQL腳本，增加了運維和溝通成本。

那么怎么寫可重復執(zhí)行的SQL腳本呢？

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

file

1 創(chuàng)建表

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

刪除表在生產(chǎn)環(huán)境是禁止的。

備份方式修改表名

修改表名： 先創(chuàng)建新表，再copy歷史數(shù)據(jù)進去，不允許刪除表；

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;

測試結(jié)果符合預期。

新增修改刪除字段

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 '地區(qū)',
    service_language  varchar(30)                        null comment '服務語種',
    agent_description varchar(500)                       null comment '客戶描述',
    status            tinyint(1)                         null comment '狀態(tài)(0=無效,1=有效)，默認為1',
    del_flag          tinyint(1)                         null comment '是否刪除(0=false,1=true)',
    user_id           bigint                             null comment '用戶id(關聯(lián)的用戶信息)',
    time_zone         varchar(50)                        null comment '時區(qū)',
    create_by         varchar(50)                        null comment '創(chuàng)建者',
    create_time       datetime default CURRENT_TIMESTAMP null comment '創(chuàng)建時間',
    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', '');

測試結(jié)果符合預期。

新增修改刪除索引

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

如果是項目后期增加索引，進行調(diào)優(yōu)，可以參考字段，寫一個存儲過程支持索引的新增可以重復執(zhí)行；

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;

  -- 獲取當前數(shù)據(jù)庫名
  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 '地區(qū)',
    service_language  varchar(30)                        null comment '服務語種',
    agent_description varchar(500)                       null comment '客戶描述',
    status            tinyint(1)                         null comment '狀態(tài)(0=無效,1=有效)，默認為1',
    del_flag          tinyint(1)                         null comment '是否刪除(0=false,1=true)',
    user_id           bigint                             null comment '用戶id(關聯(lián)的用戶信息)',
    time_zone         varchar(50)                        null comment '時區(qū)',
    create_by         varchar(50)                        null comment '創(chuàng)建者',
    create_time       datetime default CURRENT_TIMESTAMP null comment '創(chuàng)建時間',
    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');

新增數(shù)據(jù)

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

小結(jié)

按照不同的場景寫了對應的存儲過程，使得修改字段，修改索引，修改表，插入數(shù)據(jù)可以重復執(zhí)行。

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

原創(chuàng)不易，關注誠可貴，轉(zhuǎn)發(fā)價更高！轉(zhuǎn)載請注明出處，讓我們互通有無，共同進步，歡迎溝通交流。
我會持續(xù)分享Java軟件編程知識和程序員發(fā)展職業(yè)之路，歡迎關注！

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容