if not exists (select 1 from information_schema.COLUMNS where table_name='xx' and table_schema='xx' and column_name='mod') then alter table dbname.tbname add mod int; end if;
在MYSQL中无法通过一句SQL语句实现。你可以通过2楼的方法来判断指定表中是否存在这一列。
drop procedure if exists proc_add_column; delimiter $$ create PROCEDURE proc_add_column(_table varchar(30), _column varchar(20), _type varchar(50)) begin if not exists (select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME=_table and COLUMN_NAME=_column and TABLE_SCHEMA = '数据库名') then set @sqlcmd = concat('alter table ', _table, '\nadd column ', _column, ' ', _type); prepare stmt from @sqlcmd; execute stmt; deallocate prepare stmt; end if; end $$ delimiter ;call proc_add_column('_table', '_column', '_type');
alter table dbname.tbname add mod int;
end if;
drop procedure if exists proc_add_column;
delimiter $$
create PROCEDURE proc_add_column(_table varchar(30), _column varchar(20), _type varchar(50))
begin
if not exists (select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME=_table and COLUMN_NAME=_column and TABLE_SCHEMA = '数据库名') then
set @sqlcmd = concat('alter table ', _table, '\nadd column ', _column, ' ', _type);
prepare stmt from @sqlcmd;
execute stmt;
deallocate prepare stmt;
end if;
end $$
delimiter ;call proc_add_column('_table', '_column', '_type');