sql脚本示例

drop procedure if exists schema_change;
delimiter $$
create procedure schema_change() begin
if exists (select * from information_schema.columns where table_name = ‘user’ and column_name = ‘is_deleted’)
AND not EXISTS (select * from information_schema.columns where table_name = ‘user’ and column_name = ‘admin_flag’)
then
alter table user add column admin_flag bit(1) NOT NULL DEFAULT b’0’ COMMENT ‘超级管理员标识 0：普通用户 1：超级管理员’ AFTER is_deleted;
end if;

if exists (select * from information_schema.columns where table_name = ‘signet’ and column_name = ‘is_deleted’)
AND not EXISTS (select * from information_schema.columns where table_name = ‘signet’ and column_name = ‘is_locked’)
then
alter table signet add column is_locked bit(1) DEFAULT b’0’ COMMENT ‘是否锁定 0：否；1：是’ AFTER is_deleted;
end if;

if exists (select * from information_schema.columns where table_name = ‘organization’ and column_name = ‘license_src’)
AND not EXISTS (select * from information_schema.columns where table_name = ‘organization’ and column_name = ‘logo_src’)
then
alter table organization add column logo_src varchar(1024) DEFAULT NULL COMMENT ‘企业logo’ AFTER license_src;
end if;
if exists (select * from information_schema.columns where table_name = ‘organization’ and column_name = ‘logo_src’)
AND not EXISTS (select * from information_schema.columns where table_name = ‘organization’ and column_name = ‘registered_address’)
then
alter table organization add column registered_address varchar(500) DEFAULT NULL COMMENT ‘注册地址’ AFTER logo_src;
end if;

if exists (select * from information_schema.columns where table_name = ‘organization’ and column_name = ‘registered_address’)
AND not EXISTS (select * from information_schema.columns where table_name = ‘organization’ and column_name = ‘telephone’)
then
alter table organization add column telephone varchar(20) DEFAULT NULL COMMENT ‘联系电话’ AFTER registered_address;
end if;
end
$$
delimiter ;
call schema_change();