Flite Careers

Simulating Add Column if Not Exists in MySQL With Common_schema

Some MySQL DDL commands such as CREATE TABLE and DROP TABLE support an IF [NOT] EXISTS option which allows you to downgrade the error to a warning if you try to create something that already exists or drop something that doesn’t exist.

For example this gives an error:

1
2

mysql> drop table sakila.fake_table;
ERROR 1051 (42S02): Unknown table 'sakila.fake_table'

And this gives a warning:

1
2
3
4

mysql> drop table if exists sakila.fake_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Note (Code 1051): Unknown table 'sakila.fake_table'

You may also want to use IF [NOT] EXISTS for column-level changes such as ADD COLUMN and DROP COLUMN, but MySQL does not support that.

Read on for some examples of how to simulate IF [NOT] EXISTS using the QueryScript language from common_schema.

ADD COLUMN IF NOT EXISTS

This will add a new column named “foo” to the sakila.film table only if it doesn’t already exist:

1
2
3
4
5
6
7
8
9
10
11
12

call common_schema.run("
  if (
  select count(*)=0 
  from information_schema.columns 
  where table_schema = 'sakila' 
  and table_name = 'film' 
  and column_name = 'foo'
  )
  {
    alter table sakila.film add column foo tinyint unsigned not null default 0;
  }
");

DROP COLUMN IF EXISTS

This will drop the “foo” column from the sakila.film table if it exists:

1
2
3
4
5
6
7
8
9
10
11
12

call common_schema.run("
  if (
  select count(*) 
  from information_schema.columns 
  where table_schema = 'sakila' 
  and table_name = 'film' 
  and column_name = 'foo'
  )
  {
    alter table sakila.film drop column foo;
  }
");
MySQL, common_schema
Comments
Back to Top

Posted by Ike Walker

Comments