add column to mysql table if it does not exist

by

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.

More Related Contents:

  1. MySQL: ALTER TABLE if column not exists
  2. Adding multiple columns AFTER a specific column in MySQL
  3. Can a number be used to name a MySQL table column?
  4. How can I modify the size of column in a MySQL table?
  5. Error renaming a column in MySQL
  6. ALTER TABLE to add a composite primary key
  7. How to add not null constraint to existing column in MySQL
  8. Alter MySQL table to add comments on columns
  9. Using ALTER to drop a column if it exists in MySQL
  10. Create an index on a huge MySQL production table without table locking
  11. MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?
  12. ALTER table – adding AUTOINCREMENT in MySQL
  13. How to change MySQL column definition?
  14. Alter table if exists or create if doesn’t
  15. `MODIFY COLUMN` vs `CHANGE COLUMN`
  16. How do I add more members to my ENUM-type column in MySQL?
  17. ALTER TABLE without locking the table?
  18. Optimizing MySQL for ALTER TABLE of InnoDB
  19. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)
  20. How do you use MYSQL in ColdFusion? [closed]
  21. Using backticks around field names
  22. How do I do a bulk insert in mySQL using node.js
  23. Copy/duplicate database without using mysqldump
  24. MySQL variable format for a “NOT IN” list of values
  25. How to get database structure in MySQL via query
  26. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  27. How can I import a database with MySQL from terminal?
  28. Remove DEFINER clause from MySQL Dumps
  29. Encryption at rest and/or AES_ENCRYPT
  30. In MySQL what does “Overhead” mean, what is bad about it, and how to fix it?

Leave a Comment