-- 判断字段不存在则增加该字段 DROP PROCEDURE if EXISTS proc_add_column; delimiter $$ CREATE PROCEDURE `proc_add_column`(in var_table_name varchar(64),in var_column_name varchar(64),in var_sqlstr varchar(1024)) top:begin -- 表不存在则直接返回 set @p_tablenum=''; set @sqlstr1=concat('select count(table_name)into @p_tablenum from information_schema.tables where table_schema=database() and table_name=\'',var_table_name,'\' limit 1;'); prepare stmt1 from @sqlstr1; execute stmt1; deallocate prepare stmt1; if(@p_tablenum<1)then leave top; end if; -- 字段已存在则直接返回 set @p_columnnum=''; set @sqlstr=concat('select count(column_name) into @p_columnnum from information_schema.columns where table_schema=database() and table_name=\'',var_table_name,'\'and column_name =\'',var_column_name,'\';'); prepare stmt2 from @sqlstr; execute stmt2; deallocate prepare stmt2; if(@p_columnnum>0)then leave top; end if; -- 表存在且字段不存在则创建新字段 set @sqlcmd=var_sqlstr; prepare stmt3 from @sqlcmd; execute stmt3; deallocate prepare stmt3; end $$ delimiter; -- 修改表字段的函数 DROP PROCEDURE if EXISTS proc_modify_column; delimiter $$ CREATE PROCEDURE `proc_modify_column`(in var_table_name varchar(64),in var_column_name varchar(64),in var_sqlstr varchar(1024)) top:begin -- 表不存在则直接返回 set @p_tablenum=''; set @sqlstr1=concat('select count(table_name)into @p_tablenum from information_schema.tables where table_schema=database() and table_name=\'',var_table_name,'\' limit 1;'); prepare stmt1 from @sqlstr1; execute stmt1; deallocate prepare stmt1; if(@p_tablenum<1)then leave top; end if; -- 字段不存在则直接返回 set @p_columnnum=''; set @sqlstr=concat('select count(column_name) into @p_columnnum from information_schema.columns where table_schema=database() and table_name=\'',var_table_name,'\'and column_name =\'',var_column_name,'\';'); prepare stmt2 from @sqlstr; execute stmt2; deallocate prepare stmt2; if(@p_columnnum<=0)then leave top; end if; -- 表存在且字段存在则修改字段 set @sqlcmd=var_sqlstr; prepare stmt3 from @sqlcmd; execute stmt3; deallocate prepare stmt3; end $$ delimiter;
示例:
-- 新增字段
call proc_add_column( '表名', '新增字段名', 'ALTER TABLE `表名` ADD COLUMN `新增字段名` double(11, 4) NULL COMMENT ''字段说明'' AFTER`id`');
-- 修改字段
call proc_modify_column( '表名', '字段名', 'ALTER TABLE `表名` MODIFY COLUMN `字段名` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) AFTER `id`' );