Changing the Default Delimiter

In this tutorial, you will learn how to change the default delimiter by using DELIMITER command.

In MySQL program uses the delimiter semicolon(;) to separate the MySQL statements and execute each statement separately. However, a stored procedure contains multiple statement which are separated by a semicolon(;).

In creating stored procedure, you need to redefine the delimiter temporarily so that you can pass the complete stored procedure to the server as a single statement.

Syntax to redefine the delimiter.

DELIMITER delimiter_character

The delimiter_character may be a single character or multiple characters like ‘//’ or ‘$$’. But you should not use backslash(\) as it is used to escape characters in MySQL.

A stored procedure contains multiple statements separated by semicolon(;). In order to compile the complete stored procedure as a single statement, you need to temporarily modify the delimiter from the semicolon(;) to another delimiter such as $$ or //.

DELIMITER $$

CREATE PROCEDURE stored_procedure_name()
BEGIN
  //statements
  //statements
END $$

DELIMITER ;

In the above example, Firstly delimiter has been changed to $$.

And $$ after the END keyword to end the stored procedure.

And at the end, the default delimiter set back to a semicolon(;).