程序员：你如何写可重复执行的SQL语句？ - ToB企服应用市场:ToB评测及商务社交产业平台

通过使用事务，可以确保脚本的所有修改操作要么完整地执行，要么不执行。

复制代码

这样可以避免重复插入相同的数据，或者执行不必要的更新操作。

复制代码

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配置表' ;

复制代码

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

复制代码

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

复制代码