mysql 安全的操作列 add modify change drop

/**
* 参数说明
* dbName:数据库名称
* tableName:表名称
* columnName:列名称
* columnDesc:列的描述信息
* type:操作类型 1：add 2:modify 3:change 4:drop
* 调用方式
* call p_column_operation('dbName','tableName','columnName','columnDesc',type)
*/


create procedure `p_column_operation`(
in dbName text,
in tableName text,
in columnName text,
in columnDesc text,
in type int
)comment 'type 1:add 2:modify 3:change 4:drop'


begin
declare sqlStr text;
select 
count(*) into @columnFoundCnt
from
information_schema.`COLUMNS`
where
TABLE_SCHEMA=dbName and TABLE_NAME=tableName and COLUMN_NAME=columnName

if(type=1 and @columnFoundCnt<=0) then
set @sqlStr:=concat('alter table ',tableName,' add column ',columnDesc);
elseif(type=2 and @columnFoundCnt>0) then
set @sqlStr:=concat('alter table ',tableName,' modify ',columnDesc);
elseif(type=3 and @columnFoundCnt>0) then
set @sqlStr:=concat('alter table ',tableName,' change  ',columnName,' ',columnDesc);
elseif(type=4 and @columnFoundCnt>0) then
set @sqlStr:=concat('alter table ',tableName,' drop column  ',columnName);
else
set @sqlStr:=''
end if;

if(@sqlStr<>'') then
prepare stmt from @sqlStr;
execute stmt;
end if;
end