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();