程式設計師：你如何寫可重複執行的SQL語句？
2023-09-06 06:05:40
上圖的意思： 百戰百勝，屢試不爽。
故事
程式設計師小張： 剛畢業，參加工作1年左右，日常工作是CRUD
架構師老李： 多個大型專案經驗，精通各種開發架構屠龍寶術；
小張注意到，在實際的專案開發場景中，很多開發人員只關注編寫SQL指令碼來滿足功能需求，而忽略了指令碼的可重複執行性。
這就意味著，如果指令碼中的某個部分執行失敗，運維人員就必須從頭提供一個新的指令碼，這對運維團隊和開發人員來說是一個挑戰。
因此，小張決定研究如何編寫基於MySQL的可以重複執行的SQL指令碼，以提高開發效率和簡化運維流程。
他向公司的架構師老李諮詢了這個問題。老李是一位經驗豐富的架構師，
他在多個大型專案中積累了許多寶貴的經驗，精通各種開發架構屠龍寶術。
老李聽了小張的問題後，笑了笑並開始給予指導。他向小張解釋瞭如何編寫一個具有可重複執行性的SQL指令碼，並分享了以下幾個關鍵點：
a.使用事務：事務是一組SQL語句的邏輯單元，可以保證這組語句要麼全部執行成功，要麼全部回滾。
通過使用事務，可以確保指令碼的所有修改操作要麼完整地執行，要麼不執行。
b.使用條件檢查：在每個需要修改資料的語句之前，新增條件檢查以確保只有當資料不存在或滿足特定條件時才進行修改。
這樣可以避免重複插入相同的資料，或者執行不必要的更新操作。
c.錯誤處理：在編寫指令碼時，考慮到可能出現的錯誤情況，並提供適當的錯誤處理機制。例如，使用IF...ELSE語句來處理特定條件下的執行邏輯。
d.使用儲存過程：如果指令碼非常複雜，包含多個步驟和業務邏輯，可以考慮將它們封裝為儲存過程。這樣可以更好地組織和管理程式碼，並提高指令碼的可讀性和維護性。
小張聽得津津有味，他開始將老李的建議付諸實踐。他仔細研究每個SQL語句，根據老李的指導進行修改和優化。
他使用了事務來包裹整個指令碼，新增了條件檢查來避免重複插入資料，並實現了錯誤處理機制以應對異常情況。
背景
所以開發提供給到運維的SQL指令碼有一定基本要求：
1.能重複執行；
2.不出錯，（不報錯，邏輯正確）；
如果指令碼不可重複執行，則運維無法自動化，會反過來要求後端開發人員給出適配當前環境的新的SQL指令碼，增加了運維和溝通成本。
那麼怎麼寫可重複執行的SQL指令碼呢？
分成4個場景，來介紹舉例。
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!