CREATE PROCEDURE IF NOT EXISTS in MySQL

During various migrations/deployments, it is required to check to see if the stored routine (stored procedure or user-defined function) already exists before creating it. Because if it already exists, it will throw an error, and the migration will either interrupt/fail or continue without deploying the changed stored procedure.

In MySQL, the IF NOT EXISTS keyword is combined with CREATE PROCEDURE and CREATE FUNCTION to avoid an error from occurring if a procedure with the same name already exists.

As previously noted, this option is supported with both CREATE FUNCTION and CREATE PROCEDURE starting with MySQL 8.0.29.

CREATE PROCEDURE IF NOT EXISTS myProcedure()
CREATE FUNCTION IF NOT EXISTS myFunction()

If your MySQL version is prior to 8.0.29 then the following is useful.
DROP PROCEDURE IF EXISTS myProcedure;
CREATE PROCEDURE myProcedure()

Happy learning!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s