实用函数和过程
首先,我有一系列实用函数和过程,用于删除外键、普通键和列等操作。我会将它们留在数据库中,以便需要时使用。
以下是这些函数和过程:
delimiter $$
create function column_exists(ptable text, pcolumn text)
returns bool
reads sql data
begin
declare result bool;
select
count(*)
into
result
from
information_schema.columns
where
`table_schema` = 'my_database' and
`table_name` = ptable and
`column_name` = pcolumn;
return result;
end $$
create function constraint_exists(ptable text, pconstraint text)
returns bool
reads sql data
begin
declare result bool;
select
count(*)
into
result
from
information_schema.table_constraints
where
`constraint_schema` = 'my_database' and
`table_schema` = 'my_database' and
`table_name` = ptable and
`constraint_name` = pconstraint;
return result;
end $$
create procedure drop_fk_if_exists(ptable text, pconstraint text)
begin
if constraint_exists(ptable, pconstraint) then
set @stat = concat('alter table ', ptable, ' drop foreign key ', pconstraint);
prepare pstat from @stat;
execute pstat;
end if;
end $$
create procedure drop_key_if_exists(ptable text, pconstraint text)
begin
if constraint_exists(ptable, pconstraint) then
set @stat = concat('alter table ', ptable, ' drop key ', pconstraint);
prepare pstat from @stat;
execute pstat;
end if;
end $$
create procedure drop_column_if_exists(ptable text, pcolumn text)
begin
if column_exists(ptable, pcolumn) then
set @stat = concat('alter table ', ptable, ' drop column ', pcolumn);
prepare pstat from @stat;
execute pstat;
end if;
end $$
delimiter ;
使用上述工具删除约束和列
有了这些工具,很容易使用它们来检查列和约束是否存在:
-- Drop service.component_id
call drop_fk_if_exists('service', 'fk_service_1');
call drop_key_if_exists('service', 'component_id');
call drop_column_if_exists('service', 'component_id');
-- Drop commit.component_id
call drop_fk_if_exists('commit', 'commit_ibfk_1');
call drop_key_if_exists('commit', 'commit_idx1');
call drop_column_if_exists('commit', 'component_id');
-- Drop component.application_id
call drop_fk_if_exists('component', 'fk_component_1');
call drop_key_if_exists('component', 'application_id');
call drop_column_if_exists('component', 'application_id');