How do I use an IF statement with an ALTER statement in MySQL?

Question

I'm looking to add a column only if it does not already exist before. The motivation for this is that we can upgrade any version of a production instance to the latest version.

This is what I'm trying, but I keep getting a syntax error near the IF statement:

use database_name

SELECT @rowcount:=COUNT(column_name)
FROM information_schema.columns
WHERE table_name = 'table_name'
AND column_name = 'column_2';

IF @rowcount < 1 THEN
 ALTER TABLE table_name
  ADD COLUMN  column_2  VARCHAR(42)  DEFAULT  'abcd',
END IF;

commit;

What am I doing wrong?

Answer 1

You asked:

How do I use an IF statement with an ALTER statement in MySQL?

You Can't Do That™. MySQL's DML and DDL aren't as well integrated as other makes and models of table server.

You can't put DML inside stuff like IF statements or transactions. If you need to do that sort of operation you'll have to use an application programming language to issue simpler SQL.

Answer 2

I think you should change

IF @rowcount < 0 THEN ALTER TABLE table_name ADD COLUMN column_2 VARCHAR(42) DEFAULT 'abcd', END IF; and check if table exist.

Answer 3

As @Ollie Jones mentions and from looking at Using an IF Statement in a MySQL SELECT query, it looks like it's not possible to use if statements outside of sprocs or functions.

I followed the steps on http://www.cryer.co.uk/brian/mysql/howto_add_column_unless_exists.htm:

• Create the sproc (see link)
• Call the sproc when wanting to add a new column to a table

Alternatively, as @Ollie Jones mentions, you can do it at an application level, but I'm using .sql files in my case. It also saves on making multiple calls to the DB vs. having it done at the server level.