在数据库DBNAME的表TableA中增加字段newcol,如果已有此字段则不增加,我写成这样if not exists(
select 1 from information_schema.columns where table_schema='DBNAME' and table_name='TableA' and column_name='newcol'
)
alter table DBNAME.TableA add column newcol varchar(10) default null;貌似不对,请问应该怎么写。
select 1 from information_schema.columns where table_schema='DBNAME' and table_name='TableA' and column_name='newcol'
)
alter table DBNAME.TableA add column newcol varchar(10) default null;貌似不对,请问应该怎么写。
select 1 from information_schema.columns where table_schema='DBNAME' and table_name='TableA' and column_name='newcol'
) then
alter table DBNAME.TableA add column newcol varchar(10) default null;
end if
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `zz`.`zjzd`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
if not exists(
select 1 from information_schema.columns where table_schema='ZZ' and table_name='AA' and column_name='newcol'
) then
alter table ZZ.AA add column newcol varchar(10) default null;
end if;
END$$DELIMITER ;
调试通过