E Wierda E Wierda - 1 year ago 86
MySQL Question

add column to mysql table if it does not exist

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 '//'
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME='subscriber_surname' AND TABLE_NAME='#__comm_subscribers'
ALTER TABLE `#__comm_subscribers`
ADD COLUMN `subscriber_surname` varchar(64) NOT NULL default '';
delimiter ';'
CALL addcol();

Does anyone have a good way to do this?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download