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:

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:

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 //:

Code language: SQL (Structured Query Language) (sql)

Once changing the delimiter, you can use the new delimiter to end a statement as follows:

Code language: SQL (Structured Query Language) (sql)

To change the delimiter to the default one, which is a semicolon (;), you use the following statement:

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 //:

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.