check if column exists before ALTER TABLE -- mysql

Since mysql control statements (e.g. "IF") only work in stored procedures, a temporary one can be created and executed:

DROP PROCEDURE IF EXISTS add_version_to_actor;DELIMITER $$CREATE DEFINER=CURRENT_USER PROCEDURE add_version_to_actor ( ) BEGINDECLARE colName TEXT;SELECT column_name INTO colNameFROM information_schema.columns WHERE table_schema = 'connjur'    AND table_name = 'actor'AND column_name = 'version';IF colName is null THEN     ALTER TABLE  actor ADD  version TINYINT NOT NULL DEFAULT  '1' COMMENT  'code version of actor when stored';END IF; END$$DELIMITER ;CALL add_version_to_actor;DROP PROCEDURE add_version_to_actor;

Do you think you can try this?:

SELECT IFNULL(column_name, '') INTO @colNameFROM information_schema.columns WHERE table_name = 'my_table'AND column_name = 'my_column';IF @colName = '' THEN     -- ALTER COMMAND GOES HERE --END IF;

It's no one-liner, but can you at least see if it will work for you? At least while waiting for a better solution..

Utility functions and procedures

First, I have a set of utility functions and procedures that I use to do things like drop foreign keys, normal keys and columns. I just leave them in the database so I can use them as needed.

Here they are.

delimiter $$create function column_exists(ptable text, pcolumn text)  returns bool  reads sql databegin  declare result bool;  select    count(*)  into    result  from    information_schema.columns  where    `table_schema` = 'my_database' and    `table_name` = ptable and    `column_name` = pcolumn;  return result;end $$create function constraint_exists(ptable text, pconstraint text)  returns bool  reads sql databegin  declare result bool;  select    count(*)  into    result  from    information_schema.table_constraints  where    `constraint_schema` = 'my_database' and    `table_schema` = 'my_database' and    `table_name` = ptable and    `constraint_name` = pconstraint;  return result;end $$create procedure drop_fk_if_exists(ptable text, pconstraint text)begin  if constraint_exists(ptable, pconstraint) then    set @stat = concat('alter table ', ptable, ' drop foreign key ', pconstraint);    prepare pstat from @stat;    execute pstat;  end if;end $$create procedure drop_key_if_exists(ptable text, pconstraint text)begin  if constraint_exists(ptable, pconstraint) then    set @stat = concat('alter table ', ptable, ' drop key ', pconstraint);    prepare pstat from @stat;    execute pstat;  end if;end $$create procedure drop_column_if_exists(ptable text, pcolumn text)begin  if column_exists(ptable, pcolumn) then    set @stat = concat('alter table ', ptable, ' drop column ', pcolumn);    prepare pstat from @stat;    execute pstat;  end if;end $$delimiter ;

Dropping constraints and columns using the utilities above

With those in place, it is pretty easy to use them to check columns and constraints for existence:

-- Drop service.component_idcall drop_fk_if_exists('service', 'fk_service_1');call drop_key_if_exists('service', 'component_id');call drop_column_if_exists('service', 'component_id');-- Drop commit.component_idcall drop_fk_if_exists('commit', 'commit_ibfk_1');call drop_key_if_exists('commit', 'commit_idx1');call drop_column_if_exists('commit', 'component_id');-- Drop component.application_idcall drop_fk_if_exists('component', 'fk_component_1');call drop_key_if_exists('component', 'application_id');call drop_column_if_exists('component', 'application_id');