Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
In MySQL, IF statements cannot exist outside of stored procedures. Therefore, to create an idempotent migration for MySQL it's necessary to wrap the migration in a stored procedure and execute that stored procedure against the database to perform the migration.
DELIMITER $$
DROP PROCEDURE IF EXISTS add_email_address_column_to_customers_table $$
-- Create the stored procedure to perform the migration
CREATE PROCEDURE add_email_address_column_to_customers_table()
BEGIN
-- Add the email_address column to the customers table, if it doesn't already exist
IF NOT EXISTS ((SELECT * FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='customers' AND column_name='email_address')) THEN
ALTER TABLE customers ADD email_address VARCHAR(256);
END IF;
END $$
-- Execute the stored procedure
CALL add_email_address_column_to_customers_table() $$
-- Don't forget to drop the stored procedure when you're done!
DROP PROCEDURE IF EXISTS add_email_address_column_to_customers_table $$
DELIMITER ;
@delor34n

This comment has been minimized.

Copy link

commented Mar 14, 2014

THAAAAAAAAAANNNNNNNNNNNNNNNNNNNNNNNNNNKSS!!!!!!!!!!!!!!!!!!!!!

@evgkib

This comment has been minimized.

Copy link

commented Apr 23, 2014

Another way of doing this using PREPARE/EXECUTE statement:

SET @sql = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS WHERE
        table_schema='newdb'
         and table_name='persons' and column_name='id'
    ) > 0,
    "SELECT 0",
    "alter table newdb.persons add id int;"
));

PREPARE stmt FROM @sql;
EXECUTE stmt;
@Richard-Fried

This comment has been minimized.

Copy link

commented May 2, 2014

similar idea:

set @col_exists = 0;
SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable'
AND column_name='newColumn'
and table_schema = database()
into @col_exists;

set @stmt = case @col_exists
when 0 then CONCAT(
'alter table myTable'
, ' ADD COLUMN newColumn INT NULL DEFAULT 0 AFTER oldColumn'
,';')
else 'select ''column already exists, no op'''
end;

PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

@coderatchet

This comment has been minimized.

Copy link

commented Jul 7, 2014

This... This is brilliant.

@blimey74

This comment has been minimized.

Copy link

commented Apr 9, 2015

top man!

@rakeshtomar825

This comment has been minimized.

Copy link

commented Dec 3, 2016

Good man

@sinaiy

This comment has been minimized.

Copy link

commented Dec 5, 2016

this is great,
is there a way to utilize it in order to create a triggers

i can't inside a stored procedure alter the delimiter as required in order to write a create trigger statement
using prepared statement is also an issue for such actions

thanks

@reclaro

This comment has been minimized.

Copy link

commented Jan 18, 2017

Why we have to drop the store procedure at the end?
I am using a store procedure in a migration script (using flywaydb) and if I don't drop the stored procedure at the end of my script, when I try to re-run the migrations again it fails, do you have any idea why?
Thanks

@alikemaltasci

This comment has been minimized.

Copy link

commented Apr 25, 2017

thanks very much

@KMASubhani

This comment has been minimized.

Copy link

commented Feb 20, 2019

👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.