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

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

小結

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

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

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

posted @ 2023-09-05 19:46 李福春閱讀(1102) 評論(1) 收藏舉報

刷新頁面返回頂部

every day is another day!

用代碼解決問題,用代碼書寫生活.coding for my life.

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

故事

背景

1 創建表

備份方式修改表名

新增修改刪除字段

測試腳本

新增修改刪除索引

測試腳本

新增數據

小結

公告