sql存储过程 修改新增字段前判断字段是否存在

-- 判断字段不存在则增加该字段
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`'
);