mysql alter table if exists_Mysql官方文档中争对安全添加列的处理方法。Mysql Add a Column to a table if not exists...

Add a Column to a table if not exists

MySQL allows you to create a table if it does not exist, but does not provide a native way of a adding a column (i.e. a field) to an existing table with a test of whether the column already exists - so as to avoid an error if the column already exists. The ability to add a column if it does not exist can be useful for upgrade scripts and the like.

The following script creates a stored procedure that allows a column to be added to a table but only if it does not already exist:

drop procedure if existsAddColumnUnlessExists;create procedureAddColumnUnlessExists(INdbName tinytext,INtableName tinytext,INfieldName tinytext,IN fieldDef text)begin

IF NOT EXISTS(SELECT * FROMinformation_schema.COLUMNSWHERE column_name=fieldNameand table_name=tableNameand table_schema=dbName

)THEN

set @ddl=CONCAT('ALTER TABLE',dbName,'.',tableName,'ADD COLUMN',fieldName,' ',fieldDef);prepare stmt from @ddl;executestmt;END IF;end;

This stored procedure provides the functionality for "add column if not exists". To use the script call it with the name of the database, name of the table, name of the field and the field definition to use if the field is to be created. For example:

call AddColumnUnlessExists(Database(), 'accounts', 'dob', 'varchar(32) null');

which will add the field "dob" to the table "accounts" in the current database, unless it already exists, or

call AddColumnUnlessExists('GIS', 'boundaries', 'fillColour', 'int unsigned not null default 1');

which will add the field "fillColour" to the table "boundaries" in the database "GIS" if it does not already exist.

If you want to drop the stored procedure after use then use:

drop procedure AddColumnUnlessExists;