/**
* 参数说明
* 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
* 参数说明
* 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