Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save jeremyjarrell/6083251 to your computer and use it in GitHub Desktop.
Save jeremyjarrell/6083251 to your computer and use it in GitHub Desktop.
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 ;
@coderatchet
Copy link

This... This is brilliant.

@blimey74
Copy link

blimey74 commented Apr 9, 2015

top man!

@rakeshtomar825
Copy link

rakeshtomar825 commented Dec 3, 2016

Good man

@sinaiy
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
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
Copy link

thanks very much

@KMASubhani
Copy link

👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment