add column to mysql table if it does not exist

Question

My research and experiments haven't yielded an answer yet, so I am hoping for some help.

I am modifying the install file of an application which in previous versions did not have a column which I want to add now. I do not want to add the column manually, but in the installation file and only if the new column does not already exist in the table.

The table is created as follows:

CREATE TABLE IF NOT EXISTS `#__comm_subscribers` (
      `subscriber_id` int(11) NOT NULL auto_increment,
      `user_id` int(11) NOT NULL default '0',
      `subscriber_name` varchar(64) NOT NULL default '',
      `subscriber_surname` varchar(64) NOT NULL default '',
      `subscriber_email` varchar(64) NOT NULL default '',
      `confirmed` tinyint(1) NOT NULL default '0',
      `subscribe_date` datetime NOT NULL default '0000-00-00 00:00:00',
      PRIMARY KEY  (`subscriber_id`),
      UNIQUE KEY `subscriber_email` (`subscriber_email`)
    ) ENGINE=MyISAM CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' COMMENT='Subscribers for Comm are stored here.';

If I add the following, below the create table statement, then I am not sure what happens if the column already exists (and perhaps is populated):

ALTER TABLE `#__comm_subscribers` ADD `subscriber_surname`;
ALTER TABLE `#__comm_subscribers` MODIFY `subscriber_surname` varchar(64) NOT NULL default '';

So, I tried the following which I found somewhere. This does not seem to work but I am not entirely sure I used it properly.

/*delimiter '//'
CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME='subscriber_surname' AND TABLE_NAME='#__comm_subscribers'
)
THEN
    ALTER TABLE `#__comm_subscribers`
    ADD COLUMN `subscriber_surname` varchar(64) NOT NULL default '';
END IF;
END;
//
delimiter ';'
CALL addcol();
DROP PROCEDURE addcol;*/

Does anyone have a good way to do this?

Loutish

Altering information_schema.COLUMNS, i.e. what the stored procedure does, is the way to go IMHO. What part of it "does not seem to work"? – Indopacific

Answer 1

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.

Delvalle

OK this is really crude but someone's got to say it. if you are simply running a SQL script from the command line, you can give mysql the --force switch, which means keep going even if there's an error. then, just go for it. you just want to be sure that there are no statements you do NOT want to succeed if something previous has failed. – Oldie

Answer 2

Here is a working solution (just tried out with MySQL 5.0 on Solaris):

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$
CREATE PROCEDURE upgrade_database_1_0_to_2_0()
BEGIN

-- rename a table safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME='my_old_table_name') ) THEN
    RENAME TABLE 
        my_old_table_name TO my_new_table_name,
END IF;

-- add a column safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN
    ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT '';
END IF;

END $$

CALL upgrade_database_1_0_to_2_0() $$

DELIMITER ;

On a first glance it probably looks more complicated than it should, but we have to deal with following problems here:

• IF statements only work in stored procedures, not when run directly, e.g. in mysql client
• more elegant and concise SHOW COLUMNS does not work in stored procedure so have to use INFORMATION_SCHEMA
• the syntax for delimiting statements is strange in MySQL, so you have to redefine the delimiter to be able to create stored procedures. Do not forget to switch the delimiter back!
• INFORMATION_SCHEMA is global for all databases, do not forget to filter on TABLE_SCHEMA=DATABASE(). DATABASE() returns the name of the currently selected database.

Cappella

Wish I could award bonus points for explaining the issues involved that led to this approach. Thank you. – Parhelion

Answer 3

If you are on MariaDB, no need to use stored procedures. Just use, for example:

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name tinyint(1) DEFAULT 0;

See here

Wince

Brilliant! Yet another reason to use MariaDB. – Rotatory

+1 I've been working with Maria all the time and trying out all these steps above none of them worked until I got on this one, this saved my life. – Anastos

Interesting.. I never hard about MariaDB and this article can give some idea about it seravo.fi/2015/… – Kopple

This solution is great, but does require MariaDB 10.0.2. Just heads-up for anyone who wants to use this elegant solution, but is stuck on an older version. – Tropo

ALTER TABLE table_name CHANGE COLUMN IF EXISTS column_wrong_name column_name tinyint(1) DEFAULT 0; -works also good! – Apogee

Answer 4

Most of the answers address how to add a column safely in a stored procedure, I had the need to add a column to a table safely without using a stored proc and discovered that MySQL does not allow the use of IF Exists() outside a SP. I'll post my solution that it might help someone in the same situation.

SELECT count(*)
INTO @exist
FROM information_schema.columns 
WHERE table_schema = database()
and COLUMN_NAME = 'original_data'
AND table_name = 'mytable';

set @query = IF(@exist <= 0, 'alter table intent add column mycolumn4 varchar(2048) NULL after mycolumn3', 
'select \'Column Exists\' status');

prepare stmt from @query;

EXECUTE stmt;

Reasonable

Note I had to add a "LIMIT 1" to the SELECT statement when working thru the MySQL workbench GUI. – Brother

and then at the end DEALLOCATE PREPARE stmt; – Vertical

Best answer IMO! No stored procedures or permissions to create/drop them needed. Thank you. – Brower

Answer 5

Another way to do this would be to ignore the error with a declare continue handler:

delimiter ;;
create procedure foo ()
begin
    declare continue handler for 1060 begin end;
    alter table atable add subscriber_surname varchar(64);
end;;
call foo();;

I think its neater this way than with an exists subquery. Especially if you have a lot of columns to add, and you want to run the script several times.

more info on continue handlers can be found at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Goldner

Love it! Never would have thought of that. I'm going to be switching to this way of doing things for sure. – Nena

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

This is really nifty! – Tolson

Answer 6

I'm using MySQL 5.5.19.

I like having scripts that you can run and rerun without error, especially where warnings seem to linger, showing up again later while I'm running scripts that have no errors/warnings. As far as adding fields goes, I wrote myself a procedure to make it a little less typing:

-- add fields to template table to support ignoring extra data 
-- at the top/bottom of every page
CALL addFieldIfNotExists ('template', 'firstPageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageFooterBeginY', 'INT NOT NULL DEFAULT 792');

The code to create the addFieldIfNotExists procedure is as follows:

DELIMITER $$

DROP PROCEDURE IF EXISTS addFieldIfNotExists 
$$

DROP FUNCTION IF EXISTS isFieldExisting 
$$

CREATE FUNCTION isFieldExisting (table_name_IN VARCHAR(100), field_name_IN VARCHAR(100)) 
RETURNS INT
RETURN (
    SELECT COUNT(COLUMN_NAME) 
    FROM INFORMATION_SCHEMA.columns 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = table_name_IN 
    AND COLUMN_NAME = field_name_IN
)
$$

CREATE PROCEDURE addFieldIfNotExists (
    IN table_name_IN VARCHAR(100)
    , IN field_name_IN VARCHAR(100)
    , IN field_definition_IN VARCHAR(100)
)
BEGIN

    -- http://javajon.blogspot.com/2012/10/mysql-alter-table-add-column-if-not.html

    SET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);
    IF (@isFieldThere = 0) THEN

        SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ;
        SET @ddl = CONCAT(@ddl, ' ', field_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);

        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END IF;

END;
$$

I didn't write a procedure to safely modify a column, but I think the above procedure could be easily modified to do so.

Evapotranspiration

Answer 7

I've taken the OP's sproc and made it reusable and schema independent. Obviously it still requires MySQL 5.

DROP PROCEDURE IF EXISTS AddCol;

DELIMITER //

CREATE PROCEDURE AddCol(
    IN param_schema VARCHAR(100),
    IN param_table_name VARCHAR(100),
    IN param_column VARCHAR(100),
    IN param_column_details VARCHAR(100)
) 
BEGIN
    IF NOT EXISTS(
    SELECT NULL FROM information_schema.COLUMNS
    WHERE COLUMN_NAME=param_column AND TABLE_NAME=param_table_name AND table_schema = param_schema
    )
    THEN
        set @paramTable = param_table_name ;
        set @ParamColumn = param_column ;
        set @ParamSchema = param_schema;
        set @ParamColumnDetails = param_column_details;
        /* Create the full statement to execute */
        set @StatementToExecute = concat('ALTER TABLE `',@ParamSchema,'`.`',@paramTable,'` ADD COLUMN `',@ParamColumn,'` ',@ParamColumnDetails);
        /* Prepare and execute the statement that was built */
        prepare DynamicStatement from @StatementToExecute ;
        execute DynamicStatement ;
        /* Cleanup the prepared statement */
        deallocate prepare DynamicStatement ;

    END IF;
END //

DELIMITER ;

Equivocal

This works well for me. The only change I had to make was to remove the back quotes (`) in the call to concat. Also, can simplify the code by removing the variables @paramTable, @ParamColumn, @ParamSchema, and @ParamColumnDetails and just use the parameters directly. – Brunhild

Answer 8

Just tried the stored procedure script. Seems the problem is the ' marks around the delimiters. The MySQL Docs show that delimiter characters do not need the single quotes.

So you want:

delimiter //

Instead of:

delimiter '//'

Works for me :)

Michail

@Andy You completely missed the point. This comment points out where the OP made his mistake. The OP was already there if not for the single quotes. – Cripps

Answer 9

If you are running this in a script, you'll want to add the following line afterwards to make it rerunnable, otherwise you get a procedure already exists error.

drop procedure foo;

Riti

Answer 10

The best way for add the column in PHP > PDO :

$Add = $dbh->prepare("ALTER TABLE `YourCurrentTable` ADD `YourNewColumnName` INT NOT NULL");
$Add->execute();

Note: the column in the table is not repeatable, that means we don't need to check the existance of a column, but for solving the problem we check the above code:

for example if it works alert 1,if not 0, which means the column exist ! :)

Annitaanniversary

Answer 11

Check if Column Exist or not in PDO (100%)

{
    if(isset($_POST['Add']))
    {
        $ColumnExist = $dbh->prepare("SELECT * FROM ColumnChecker where column_name='$insert_column_name' LIMIT 1");
        $ColumnExist ->execute();
        $ColumnName = $ColumnExist->fetch(2);
        $Display_Column_Name = $ColumnName['column_name'];

        if($Display_Column_Name == $insert_column_name)
        {
            echo "$Display_Column_Name already exist";
        } //*****************************
        else 
        {
            $InsertColumn = $dbh->prepare("insert into ColumnChecker ( column_name ) values ('$insert_column_name')");
            $InsertColumn->execute();

            if($InsertColumn)
            {
                $Add = $dbh->prepare("ALTER TABLE `$Table` ADD `$insert_column_name` $insert_column_type($insert_column_Length) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ");
                $Add->execute();

                if($Add)
                {
                    echo 'Table has been updated';  
                }
                else 
                {
                    echo 'Sorry! Try again...'; 
                }
            }   
        }
    }
}#Add Column into Table :)

Annitaanniversary

Answer 12

Procedure from Jake https://mcmap.net/q/187642/-add-column-to-mysql-table-if-it-does-not-exist is very simple and good solution for adding new columns, but with one additional line:

DROP PROCEDURE IF EXISTS foo;;

you can add new columns later there, and it will work next time too:

delimiter ;;
DROP PROCEDURE IF EXISTS foo;;
create procedure foo ()
begin
    declare continue handler for 1060 begin end;
    alter table atable add subscriber_surname varchar(64);
    alter table atable add subscriber_address varchar(254);
end;;
call foo();;

Aplomb

Answer 13

$smpt = $pdo->prepare("SHOW fields FROM __TABLE__NAME__");
$smpt->execute();
$res = $smpt->fetchAll(PDO::FETCH_ASSOC);
//print_r($res);

Then in $res by cycle look for key of your column Smth like this:

    if($field['Field'] == '_my_col_'){
       return true;
    }
+

**Below code is good for checking column existing in the WordPress tables:**
public static function is_table_col_exists($table, $col)
    {
        global $wpdb;
        $fields = $wpdb->get_results("SHOW fields FROM {$table}", ARRAY_A);
        foreach ($fields as $field)
        {
            if ($field['Field'] == $col)
            {
                return TRUE;
            }
        }

        return FALSE;
    }

Impressible

this can be done a litle more efficient SHOW fields FROM __TABLE__NAME__ where field='_my_col_'; and then check for the result set to be non-empty – Fellner

Answer 14

Below are the Stored procedure in MySQL To Add Column(s) in different Table(s) in different Database(s) if column does not exists in a Database(s) Table(s) with following advantages

• multiple columns can be added use at once to alter multiple Table in different Databases
• three mysql commands run, i.e. DROP, CREATE, CALL For Procedure
• DATABASE Name should be changes as per USE otherwise problem may occur for multiple datas

DROP PROCEDURE  IF EXISTS `AlterTables`;
DELIMITER $$
CREATE PROCEDURE `AlterTables`() 
BEGIN
    DECLARE table1_column1_count INT;
    DECLARE table2_column2_count INT;
    SET table1_column1_count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_SCHEMA = 'DATABASE_NAME' AND
			    TABLE_NAME = 'TABLE_NAME1' AND 
                            COLUMN_NAME = 'TABLE_NAME1_COLUMN1');
    SET table2_column2_count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_SCHEMA = 'DATABASE_NAME' AND
			    TABLE_NAME = 'TABLE_NAME2' AND 
                            COLUMN_NAME = 'TABLE_NAME2_COLUMN2');
    IF table1_column1_count = 0 THEN
        ALTER TABLE `TABLE_NAME1`ADD `TABLE_NAME1_COLUMN1` text COLLATE 'latin1_swedish_ci' NULL AFTER `TABLE_NAME1_COLUMN3`,COMMENT='COMMENT HERE';
    END IF;
    IF table2_column2_count = 0 THEN
        ALTER TABLE `TABLE_NAME2` ADD `TABLE_NAME2_COLUMN2` VARCHAR( 100 ) NULL DEFAULT NULL COMMENT 'COMMENT HERE';
    END IF;
END $$
DELIMITER ;
call AlterTables();

Futures

Answer 15

Here is my PHP/PDO solution to do that :

function addColumnIfNotExists($db, $table, $col, $type, $null = true) {
    global $pdo;
    if($res = $pdo->query("SHOW COLUMNS FROM `$db`.`$table` WHERE `Field`='$col'")) {
        if(!$res = $res->fetchAll(PDO::FETCH_ASSOC)) {
            $null = ($null ? 'NULL' : 'NOT NULL');
            $pdo->query("ALTER TABLE `$db`.`$table` ADD `$col` $type $null");
        }
    }
}

Note that it will automatically add the column in the end of the table. I havent implemented the DEFAULT part of query.

Biernat

Answer 16

ALTER TABLE `subscriber_surname` ADD  IF NOT EXISTS  `#__comm_subscribers`.`subscriber_surname`;

ALTER TABLE `#__comm_subscribers` MODIFY `subscriber_surname` varchar(64) NOT NULL default '';

Jonajonah

It would be great if you could add some description to your solution se we (the users) could understand the advantages of this solution in spite of the others. This is an improvement for this and future answers. – Darg