Add column if not exist

Question

How could I want to add a column if not exist. I tried this code:

IF EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'forwind.measuringdata_fino_10_00000000'
             AND table_schema = 'forwind'
             AND column_name != 'F1_USA(40)_u')  THEN

  ALTER TABLE `forwind.measuringdata_fino_10_00000000` ADD `F1_USA(40)_u` FLOAT NOT NULL default '0';

END IF; 

but I get the following error:

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS

Good for me if somebody know an other solution!

Answer 1

MySQL does not support anonymous IF statements like that.

You have at least 3 options to address this:

1) Just run the ALTER TABLE statement and ignore the Duplicate column name error if the column already exists. If you're executing a bunch of DDL in a script you can use mysql -f to ignore the errors and keep going. 2) Use a scripting language such as bash, python, perl etc to check for the existence of the column and then add it if it does not already exist. 3) Create a stored procedure in MySQL to check for the existence of the column and then add it if it does not already exist.

P.S. As an aside, I recommend against putting parentheses in column names, because that forces you to quote the column name every time you reference it.