MySQL 表添加字段 存储过程实现 IF NOT EXISTS

-- 创建表语句（对于已经存在的表不再建立）
create table IF NOT EXISTS t1(id int not null);
create table IF NOT EXISTS t2(id int not null);


-- 添加字段存储过程（对于已经存在的字段不再添加）
DROP PROCEDURE IF EXISTS p_alter_table;
delimiter //

CREATE PROCEDURE p_alter_table()
begin
-- table t1
IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='test' AND table_name='t1' AND COLUMN_NAME='name') 
   and EXISTS(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND table_name='t1') THEN
     ALTER TABLE t1 ADD column name VARCHAR(30);
END IF;

-- table t1
IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='test' AND table_name='t2' AND COLUMN_NAME='name') 
   and EXISTS(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND table_name='t2') THEN
     ALTER TABLE t2 ADD column name VARCHAR(50);
END IF;

end;
//

delimiter ;

call p_alter_table();