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;

Answer 2

you can create a procedure for the query,

DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_NAME = 'email_subscription' AND 
                            COLUMN_NAME = 'subscribe_all');
    IF _count = 0 THEN
        ALTER TABLE email_subscription
            ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
            ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;
    END IF;
END $$
DELIMITER ;

Answer 3

You are using MS SQL Server syntax in MySQL.

Answer 4

There is no equivalent syntax to achieve this in a single MySQL statement.

To get something simlilar, you can either

1) attempt to add the column with an ALTER TABLE, and let MySQL raise an error if a column of that name already exists in the table, or

2) query the information_schema.columns view to check if a column of that name exists in the table.

Note that you really do need to check for the table_schema, as well as the table_name:

SELECT column_name
  FROM information_schema.columns 
 WHERE table_schema = 'foo'
   AND table_name   = 'email_subscription'
   AND column_name  = 'subscribe_all'

and based on that, decide whether to run the ALTER TABLE

Answer 5

Also add condition for database name to check column existance.

Try this:

DELIMITER $$
CREATE PROCEDURE sp_AlterTable()
BEGIN
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_SCHEMA = 'dbName' AND 
                         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;
    END IF; 
END $$
DELIMITER ;

Answer 6

SET @s = (SELECT IF(
    (SELECT COUNT(column_name)
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE table_name = 'oc_bitcoin_wallets_receive'
          AND table_schema = 'k_opencart2'
          AND column_name = 'test3'
    ) > 0,
    "SELECT 1",
    "ALTER TABLE `oc_bitcoin_wallets_receive` ADD COLUMN `test3` INT NOT NULL AFTER `test2`;"
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Values for edit: oc_bitcoin_wallets_receive - table name, k_opencart2 - database name, test3 - name of new column, oc_bitcoin_wallets_receive - second location table test3 - second location column, test2 - name of column before new column.

Answer 7

I am late to the party but a universal solution that works for all mysql servers is;

$query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'yourtablename' AND table_schema = DATABASE() AND column_name = 'yourcolumnname'"; $result=mysqli_query($link, $query); if(mysqli_num_rows($result) == 0){ $query2 = ("ALTER TABLE yourtablename ADD yourcolumname VARCHAR( 100 ) NOT NULL"); $create = mysqli_query($link, $query2); }