MySQL Delimiter
Summary: in this tutorial, you will learn how to change the default MySQL delimiter by using the DELIMITER
command.
When writing SQL statements, you use the semicolon (;
) to separate two statements like the following example:
SELECT * FROM products;
SELECT * FROM customers;Code language: SQL (Structured Query Language) (sql)
A MySQL client program such as MySQL Workbench or mysql program uses the delimiter (;
) to separate statements and executes each statement separately.
However, a stored procedure consists of multiple statements separated by a semicolon (;).
If you use a MySQL client program to define a stored procedure that contains semicolon characters, the MySQL client program will not treat the whole stored procedure as a single statement, but many statements.
Therefore, you must redefine the delimiter temporarily so that you can pass the whole stored procedure to the server as a single statement.
To redefine the default delimiter, you use the DELIMITER
command:
DELIMITER delimiter_character
Code language: SQL (Structured Query Language) (sql)
The delimiter_character
may consist of a single character or multiple characters e.g., //
or $$
. However, you should avoid using the backslash (\
) because it’s the escape character in MySQL.
For example, the following statement changes the current delimiter to //
:
DELIMITER //
Code language: SQL (Structured Query Language) (sql)
Once changing the delimiter, you can use the new delimiter to end a statement as follows:
DELIMITER //
SELECT * FROM customers //
SELECT * FROM products //
Code language: SQL (Structured Query Language) (sql)
To change the delimiter to the default one, which is a semicolon (;
), you use the following statement:
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
Using MySQL DELIMITER for stored procedures
Typically, a stored procedure contains multiple statements separated by semicolons (;). To compile the whole stored procedure as a single compound statement, you need to temporarily change the delimiter from the semicolon (;) to another delimiter such as $$
or //
:
DELIMITER $$
CREATE PROCEDURE sp_name()BEGIN
— statements
END $$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
Note that you will learn the syntax of creating a stored procedure in the next tutorial.
In this code:
- First, change the default delimiter to
$$
. - Second, use (
;
) in the body of the stored procedure and$$
after theEND
keyword to end the stored procedure. - Third, change the default delimiter back to a semicolon (;)
In this tutorial, you have learned how to use the MySQL DELIMITER
command to change the default delimiter (;) to another.