mysql 判断字段是否存在, 不存在则添加

DROP PROCEDURE IF EXISTS pro_AddColumn;

DELIMITER $$

CREATE PROCEDURE pro_AddColumn( 
	IN bdname VARCHAR(50), 
	IN tablename VARCHAR(50), 
	IN colname VARCHAR(50),
	IN coltype VARCHAR(20),
	IN coldesc VARCHAR(50) )

BEGIN
	if not exists(select 1 from information_schema.`COLUMNS` where table_schema=bdname and table_name=tablename and column_name=colname) then
		set @sqlStr = CONCAT('alter table ', bdname, '.', tablename, ' add ', colname, ' ',coltype,' ', ' COMMENT ',  coldesc);
    prepare sqlStr from @sqlStr;
    execute sqlStr;
	end if;
END $$

DELIMITER;
-- 库名, 表名, 字段名, 字段类型, 注释
CALL pro_AddColumn('bd_dev', 'apply_customer', 'audit_user_id ', 'BIGINT', '''审核员id''');
CALL pro_AddColumn('bd_dev', 'apply_customer', 'audit_user_name', 'VARCHAR(20)', '''审核员姓名''');
DROP PROCEDURE pro_AddColumn;