MySQL Conditional Alter Table Add Column

sp_add_column_if_not_exists.sql

DROP PROCEDURE IF EXISTS `sp_add_column_if_not_exists`;

DELIMITER !!

CREATE PROCEDURE sp_add_column_if_not_exists (
  IN table_name nvarchar(128),
  IN column_name nvarchar(128),
  IN column_definition varchar(256)
)
BEGIN
  SET @chk_exists = NULL;
-- # Can't pass procedure arguments to prepared statements, only user variables:
  SET @t = table_name;
  SET @c = column_name;

  SET @tpl_check_exists = 'SELECT TRUE INTO @chk_exists FROM INFORMATION_SCHEMA.COLUMNS
    WHERE `table_name` = ? AND `column_name` = ?';

-- # PREPARE can only parameterize data values, so must concat an ALTER TABLE
  SET @tpl_add_column = CONCAT(
    'ALTER TABLE `',table_name,'` ADD COLUMN ',column_name,' ',column_definition
  );

  PREPARE chk_exists FROM @tpl_check_exists;
  PREPARE add_column FROM @tpl_add_column;

  EXECUTE chk_exists USING @t, @c;

  IF (@chk_exists IS NULL ) THEN
    EXECUTE add_column;
  END IF;

  DEALLOCATE PREPARE chk_exists;
  DEALLOCATE PREPARE add_column;

END!!

DELIMITER ;

-- # SAMPLE USAGE:

CREATE TABLE IF NOT EXISTS `jos_ms_email_cache` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY ( `id`)
);

CALL sp_add_column_if_not_exists('jos_ms_email_cache', 'body', 'text NOT NULL');

SHOW CREATE TABLE `jos_ms_email_cache`;