e_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_INDEXE