Mysql: Alter Table If Column Not Exists

MySQL: ALTER TABLE if column not exists

Use the following in a stored procedure:

IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tablename'
             AND table_schema = 'db_name'
             AND column_name = 'columnname')  THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

add column to mysql table if it does not exist

Note that INFORMATION_SCHEMA isn't supported in MySQL prior to 5.0. Nor are stored procedures supported prior to 5.0, so if you need to support MySQL 4.1, this solution isn't good.

One solution used by frameworks that use database migrations is to record in your database a revision number for the schema. Just a table with a single column and single row, with an integer indicating which revision is current in effect. When you update the schema, increment the number.

Another solution would be to just try the ALTER TABLE ADD COLUMN command. It should throw an error if the column already exists.

ERROR 1060 (42S21): Duplicate column name 'newcolumnname'

Catch the error and disregard it in your upgrade script.

MySQL add column if not exist

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 ;

MySql - Alter Table and add column if not exists

I have altered the code under for loop as all the columns are VARCHARS and using backtick worked out.

$col_arr = array( 
    'proj_desc', 
    'companyname', 
    'text_color', 
    'citation_color', 
    'bg_color', 
    'border_color', 
    'ratingsformat', 
    'version', 
    'rating', 
    'customratings', 
    'speed', 
    'pagination', 
    'global_option'
);

foreach ($col_arr as  $column_field) {
    $col = mysqli_query($link, "SELECT `$column_field` FROM `setting`");
    if(!$col){
        mysqli_query($link, "ALTER TABLE  `setting`
            ADD  `$column_field` VARCHAR( 255 )"
        );
    }
}

Let me know if this code is correct.

MemSql > add column if does not exist

You can check if a column exists in SQL by querying information_schema.columns and seeing if it is present there - e.g.

select count(*) from information_schema.columns where table_name = 't' and column_name = 'c'

You can add a column with ALTER TABLE ADD COLUMN.

---

Related Topics