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

delor34n commented Mar 14, 2014

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

@evgkib

This comment has been minimized.

Copy link

evgkib 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

Richard-Fried 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

coderatchet commented Jul 7, 2014

This... This is brilliant.

@blimey74

This comment has been minimized.

Copy link

blimey74 commented Apr 9, 2015

top man!

@rakeshtomar825

This comment has been minimized.

Copy link

rakeshtomar825 commented Dec 3, 2016

Good man

@sinaiy

This comment has been minimized.

Copy link

sinaiy 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

reclaro 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

alikemaltasci commented Apr 25, 2017

thanks very much

@KMASubhani

This comment has been minimized.

Copy link

KMASubhani 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.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.