MySQL: The reason for using the “DELIMITER” statement in MySQL routines (stored procedures, functions, triggers)

This short article illustrates how to format your MySQL routines (stored procedures, functions, triggers) for release. We’ll use the following code block to discuss.


DROP PROCEDURE IF EXISTS mytestproc; -- Don't use "dbname.mytestproc"
 
DELIMITER $$ -- Change delimiter from semicolon
 
CREATE PROCEDURE mytestproc() -- Again, don't use "dbname.mytestproc"
BEGIN
# ATS Created test for dbname data 06/20/2013
SELECT 1;
 
END$$ -- Use of new $$ delimiter
 
DELIMITER ; -- Change delimiter back to the default (semicolon). Note that the spacing used here is important!

1) The first step in the script is to drop the existing routine, if it exists. Notice how the procedure name is not prefixed with database name (e.g., dbname.mytestproc). This is so that we can run this same script against multiple databases.

2) The next step is to change the default MySQL script parser’s delimiter from semicolon (;) to double-dollar sign ($$). The reason you do this is so that the semicolons after each statement in the body of the routine are not interpreted by the parser as meaning the end of the CREATE PROCEDURE statement. This is because the entire CREATE PROCEDURE block, from CREATE PROCEDURE to END is actually a single statement that must be executed by itself. Were it not for the delimiter change, the script would break, since there each statement inside BEGIN and END would execute individually. Note that you can use a variety of non-reserved characters to make your own custom delimiter.

3) The next step is to create the routine.

4) Next is the BEGIN statement, indicating the start of the routine body

5) Next are the statements inside the routine, each terminated with semicolon (which will NOT be interpreted as end-of-CREATE PROCEDURE, since you changed the delimiter to $$).

6) Next is the END$$ statement. Notice how END is followed by $$. This actually ends the CREATE PROCEDURE statement.

7) Finally, the default delimiter (semicolon) is restored

Author: Alex T. Silverstein

A developer who wants to help you and himself by writing down good tidbits for free

11 thoughts on “MySQL: The reason for using the “DELIMITER” statement in MySQL routines (stored procedures, functions, triggers)”

  1. Thanks for that clear explanation of the delimiter, coming from MSsql to Mysql I was unclear as to why it was needed

  2. Hi i am new in mysql triggers why we should start the statement DELIMITER $$.In case i forgot the statement delimiter, start CREATE PROCEDURE mytestproc().what will happen in next.please help me

    1. ThadiKaran, what would happen is that as soon as a semi-colon-terminated statement was encountered in the body of your procedure, that statement would be executed, effectively ending the creation of your procedure at that point, as opposed to only creating the procedure when your substituted delimited was encountered (i.e., after the last END in the procedure body).

  3. This is a newbie question.. If the first line DROP PROCEDURE IF EXISTS mytestproc; did not exist, what repercussions would there be?

  4. Hi, this is a newbie question… if the first line of code – DROP PROCEDURE IF EXISTS mytestproc; did not exist, what would happen? Is it just good practice to put DROP PROCEDURE IF EXISTS as the first line of code? Thanks.

  5. Why does MS sql not have to use the delimeter statement. It just runs without screwing up the procedure and sometimes the database.

Leave a Reply

Your email address will not be published. Required fields are marked *