MySQL add column if not exist

Question

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS   WHERE TABLE_NAME = 'email_subscription' AND COLUMN_NAME = 'subscribe_all')   THEN    ALTER TABLE email_subscription   ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,   ADD COLUMN subscribe_category varchar(512) DEFAULT NULL; 

I had a look at huge amount of examples. but this query doesn't work, I got error of:

ERROR 1064 (42000): 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 NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =' at line 1

Answer 1

If your host doesn't give you permission to create or run procedures, I think I found another way to do this using PREPARE/EXECUTE and querying the schema:

SET @s = (SELECT IF(     (SELECT COUNT(*)         FROM INFORMATION_SCHEMA.COLUMNS         WHERE table_name = 'table_name'         AND table_schema = DATABASE()         AND column_name = 'col_name'     ) > 0,     "SELECT 1",     "ALTER TABLE table_name ADD col_name VARCHAR(100)" ));  PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;