MySQL Delimiter

Created with Sketch.

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 the END 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.

Leave a Reply

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