上圖的意思： 百戰(zhàn)百勝，屢試不爽。
故事
程序員小張： 剛畢業(yè)，參加工作1年左右，日常工作是CRUD
架構(gòu)師老李： 多個(gè)大型項(xiàng)目經(jīng)驗(yàn)，精通各種開(kāi)發(fā)架構(gòu)屠龍寶術(shù)；
小張注意到，在實(shí)際的項(xiàng)目開(kāi)發(fā)場(chǎng)景中，很多開(kāi)發(fā)人員只關(guān)注編寫(xiě)SQL腳本來(lái)滿足功能需求，而忽略了腳本的可重復(fù)執(zhí)行性。
這就意味著，如果腳本中的某個(gè)部分執(zhí)行失敗，運(yùn)維人員就必須從頭提供一個(gè)新的腳本，這對(duì)運(yùn)維團(tuán)隊(duì)和開(kāi)發(fā)人員來(lái)說(shuō)是一個(gè)挑戰(zhàn)。
因此，小張決定研究如何編寫(xiě)基于MySQL的可以重復(fù)執(zhí)行的SQL腳本，以提高開(kāi)發(fā)效率和簡(jiǎn)化運(yùn)維流程。
他向公司的架構(gòu)師老李咨詢了這個(gè)問(wèn)題。老李是一位經(jīng)驗(yàn)豐富的架構(gòu)師，
他在多個(gè)大型項(xiàng)目中積累了許多寶貴的經(jīng)驗(yàn)，精通各種開(kāi)發(fā)架構(gòu)屠龍寶術(shù)。
老李聽(tīng)了小張的問(wèn)題后，笑了笑并開(kāi)始給予指導(dǎo)。他向小張解釋了如何編寫(xiě)一個(gè)具有可重復(fù)執(zhí)行性的SQL腳本，并分享了以下幾個(gè)關(guān)鍵點(diǎn)：
a.使用事務(wù)：事務(wù)是一組SQL語(yǔ)句的邏輯單元，可以保證這組語(yǔ)句要么全部執(zhí)行成功，要么全部回滾。
通過(guò)使用事務(wù)，可以確保腳本的所有修改操作要么完整地執(zhí)行，要么不執(zhí)行。
b.使用條件檢查：在每個(gè)需要修改數(shù)據(jù)的語(yǔ)句之前，添加條件檢查以確保只有當(dāng)數(shù)據(jù)不存在或滿足特定條件時(shí)才進(jìn)行修改。
這樣可以避免重復(fù)插入相同的數(shù)據(jù)，或者執(zhí)行不必要的更新操作。
c.錯(cuò)誤處理：在編寫(xiě)腳本時(shí)，考慮到可能出現(xiàn)的錯(cuò)誤情況，并提供適當(dāng)?shù)腻e(cuò)誤處理機(jī)制。例如，使用IF...ELSE語(yǔ)句來(lái)處理特定條件下的執(zhí)行邏輯。
d.使用存儲(chǔ)過(guò)程：如果腳本非常復(fù)雜，包含多個(gè)步驟和業(yè)務(wù)邏輯，可以考慮將它們封裝為存儲(chǔ)過(guò)程。這樣可以更好地組織和管理代碼，并提高腳本的可讀性和維護(hù)性。
小張聽(tīng)得津津有味，他開(kāi)始將老李的建議付諸實(shí)踐。他仔細(xì)研究每個(gè)SQL語(yǔ)句，根據(jù)老李的指導(dǎo)進(jìn)行修改和優(yōu)化。
他使用了事務(wù)來(lái)包裹整個(gè)腳本，添加了條件檢查來(lái)避免重復(fù)插入數(shù)據(jù)，并實(shí)現(xiàn)了錯(cuò)誤處理機(jī)制以應(yīng)對(duì)異常情況。
背景
所以開(kāi)發(fā)提供給到運(yùn)維的SQL腳本有一定基本要求：
1.能重復(fù)執(zhí)行；
2.不出錯(cuò)，（不報(bào)錯(cuò)，邏輯正確）；
如果腳本不可重復(fù)執(zhí)行，則運(yùn)維無(wú)法自動(dòng)化，會(huì)反過(guò)來(lái)要求后端開(kāi)發(fā)人員給出適配當(dāng)前環(huán)境的新的SQL腳本，增加了運(yùn)維和溝通成本。
那么怎么寫(xiě)可重復(fù)執(zhí)行的SQL腳本呢？
分成4個(gè)場(chǎng)景，來(lái)介紹舉例。
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)建時(shí)間',
primary key(id)
)ENGINE=InnoDB comment 'nginx配置表' ;
刪除表在生產(chǎn)環(huán)境是禁止的。
備份方式修改表名
修改表名： 先創(chuàng)建新表，再copy歷史數(shù)據(jù)進(jìn)去，不允許刪除表；
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 ;
測(cè)試腳本：
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;
測(cè)試結(jié)果符合預(yù)期。
新增修改刪除字段
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;
測(cè)試腳本
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 '客服類型(場(chǎng)地客服、直聘客服)',
district varchar(30) null comment '地區(qū)',
service_language varchar(30) null comment '服務(wù)語(yǔ)種',
agent_description varchar(500) null comment '客戶描述',
status tinyint(1) null comment '狀態(tài)(0=無(wú)效,1=有效)，默認(rèn)為1',
del_flag tinyint(1) null comment '是否刪除(0=false,1=true)',
user_id bigint null comment '用戶id(關(guān)聯(lián)的用戶信息)',
time_zone varchar(50) null comment '時(shí)區(qū)',
create_by varchar(50) null comment '創(chuàng)建者',
create_time datetime default CURRENT_TIMESTAMP null comment '創(chuàng)建時(shí)間',
update_by varchar(50) null comment '修改者',
update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '修改時(shí)間'
) 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', '');
測(cè)試結(jié)果符合預(yù)期。
新增修改刪除索引
一般放在建表語(yǔ)句中，80%的情況；
如果是項(xiàng)目后期增加索引，進(jìn)行調(diào)優(yōu)，可以參考字段，寫(xiě)一個(gè)存儲(chǔ)過(guò)程支持索引的新增可以重復(fù)執(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;
-- 獲取當(dāng)前數(shù)據(jù)庫(kù)名
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 ;
測(cè)試腳本
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 '客服類型(場(chǎng)地客服、直聘客服)',
district varchar(30) null comment '地區(qū)',
service_language varchar(30) null comment '服務(wù)語(yǔ)種',
agent_description varchar(500) null comment '客戶描述',
status tinyint(1) null comment '狀態(tài)(0=無(wú)效,1=有效)，默認(rèn)為1',
del_flag tinyint(1) null comment '是否刪除(0=false,1=true)',
user_id bigint null comment '用戶id(關(guān)聯(lián)的用戶信息)',
time_zone varchar(50) null comment '時(shí)區(qū)',
create_by varchar(50) null comment '創(chuàng)建者',
create_time datetime default CURRENT_TIMESTAMP null comment '創(chuàng)建時(shí)間',
update_by varchar(50) null comment '修改者',
update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '修改時(shí)間'
)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語(yǔ)句 按照主鍵或者唯一值，存在則先刪除再插入，不存在則直接插入；
注意： 一定要寫(xiě)字段名稱。
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/**"}}]', '{}', '云樞服務(wù)app-metadata-runtime', '2020-07-31 21:44:11', '2020-09-07 20:24:13', 0);
小結(jié)
按照不同的場(chǎng)景寫(xiě)了對(duì)應(yīng)的存儲(chǔ)過(guò)程，使得修改字段，修改索引，修改表，插入數(shù)據(jù)可以重復(fù)執(zhí)行。
如果有使用問(wèn)題或者優(yōu)化建議，歡迎提出來(lái)。還原跟我交流 ;文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-695028.html
原創(chuàng)不易，關(guān)注誠(chéng)可貴，轉(zhuǎn)發(fā)價(jià)更高！轉(zhuǎn)載請(qǐng)注明出處，讓我們互通有無(wú)，共同進(jìn)步，歡迎溝通交流。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-695028.html
到了這里，關(guān)于程序員：你如何寫(xiě)可重復(fù)執(zhí)行的SQL語(yǔ)句？的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
