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

User · Answer

ALTER TABLE  subscriber surname  ADD  IF NOT EXISTS      comm subscribers   subscriber surname    ALTER TABLE     comm subscribers  MODIFY  subscriber surname  varchar 64  NOT NULL default

User · Answer

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  lt   0   alter table intent add column mycolumn4 varchar 2048  NULL after mycolumn3     select   Column Exists   status     prepare stmt from  query   EXECUTE stmt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Procedure from Jake https   stackoverflow com a 6476091 6751901 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

User · Answer

Check if Column Exist or not in PDO  100          if isset   POST  Add                    ColumnExist    dbh- gt prepare  SELECT   FROM ColumnChecker where column name   insert column name  LIMIT 1             ColumnExist - gt execute             ColumnName    ColumnExist- gt fetch 2            Display Column Name    ColumnName  column name             if  Display Column Name     insert column name                        echo   Display Column Name already exist                                                     else                         InsertColumn    dbh- gt prepare  insert into ColumnChecker   column name   values    insert column name                   InsertColumn- gt execute                 if  InsertColumn                                 Add    dbh- gt prepare  ALTER TABLE   Table  ADD   insert column name   insert column type  insert column Length  CHARACTER SET utf8 COLLATE utf8 general ci NOT NULL                      Add- gt execute                     if  Add                                        echo  Table has been updated                                       else                                        echo  Sorry  Try again                                                            Add Column into Table

User · Answer

Here is my PHP PDO solution to do that   function addColumnIfNotExists  db   table   col   type   null   true        global  pdo      if  res    pdo- gt query  quot SHOW COLUMNS FROM   db    table  WHERE  Field    col  quot              if   res    res- gt fetchAll PDO  FETCH ASSOC                  null     null    NULL     NOT NULL                 pdo- gt query  quot ALTER TABLE   db    table  ADD   col   type  null quot                        Note that it will automatically add the column in the end of the table  I havent implemented the DEFAULT part of query

User · Answer

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

User · Answer

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

User · Answer

The best way for add the column in PHP   PDO      Add    dbh- gt prepare  ALTER TABLE  YourCurrentTable  ADD  YourNewColumnName  INT NOT NULL     Add- gt 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

User · Answer

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

User · Answer

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    x000D   x000D  DROP PROCEDURE  IF EXISTS  AlterTables   x000D  DELIMITER    x000D  CREATE PROCEDURE  AlterTables     x000D  BEGIN x000D      DECLARE table1 column1 count INT  x000D      DECLARE table2 column2 count INT  x000D      SET table1 column1 count      SELECT COUNT     x000D                      FROM INFORMATION SCHEMA COLUMNS x000D                      WHERE   TABLE SCHEMA    DATABASE NAME  AND x000D         TABLE NAME    TABLE NAME1  AND  x000D                              COLUMN NAME    TABLE NAME1 COLUMN1    x000D      SET table2 column2 count      SELECT COUNT     x000D                      FROM INFORMATION SCHEMA COLUMNS x000D                      WHERE   TABLE SCHEMA    DATABASE NAME  AND x000D         TABLE NAME    TABLE NAME2  AND  x000D                              COLUMN NAME    TABLE NAME2 COLUMN2    x000D      IF table1 column1 count   0 THEN x000D          ALTER TABLE  TABLE NAME1 ADD  TABLE NAME1 COLUMN1  text COLLATE  latin1 swedish ci  NULL AFTER  TABLE NAME1 COLUMN3  COMMENT  COMMENT HERE   x000D      END IF  x000D      IF table2 column2 count   0 THEN x000D          ALTER TABLE  TABLE NAME2  ADD  TABLE NAME2 COLUMN2  VARCHAR  100   NULL DEFAULT NULL COMMENT  COMMENT HERE   x000D      END IF  x000D  END    x000D  DELIMITER   x000D  call AlterTables    x000D   x000D   x000D

User · Answer

smpt    pdo- gt prepare  SHOW fields FROM   TABLE  NAME       smpt- gt execute     res    smpt- gt 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- gt get results  SHOW fields FROM   table    ARRAY A           foreach   fields as  field                        if   field  Field       col                                return TRUE                                   return FALSE

[mysql] add column to mysql table if it does not exist

Examples related to mysql