MySQL Delimiter

Summary: in this tutorial, you will learn how to change the default MySQL delimiter by using the DELIMITER command.

When you want to execute multiple SQL statements, you use the semicolon (;) to separate two statements, as shown in the following example:

SELECT * FROM products;

SELECT * FROM customers;

A MySQL client program, such as MySQL Workbench or the mysql program, uses the default delimiter (;) to separate statements and execute each 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 semicolons, the MySQL client program will not treat the entire stored procedure as a single statement; instead, it will recognize it as multiple statements.

Therefore, it is necessary to temporarily redefine the delimiter so that you can pass the entire stored procedure to the server as a single statement.

To redefine the default delimiter, you use the DELIMITER command as follows:

DELIMITER delimiter_character

The delimiter_character may consist of a single character or multiple characters, such as // or $$. However, you should avoid using the backslash (\) because it’s the escape character in MySQL.

MySQL uses backslash (\) as the escape character, which allows you to include special characters within strings without triggering syntax errors. For example, you can use the escape character to include a single quote in a string like this: SELECT 'It\'s a sunny day';

The following example illustrates how to change the current delimiter to //:

DELIMITER //

After you change the delimiter, you can use the new delimiter to end a statement, as follows:

DELIMITER //

SELECT * FROM customers //

SELECT * FROM products //

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

DELIMITER ;

Utilizing MySQL DELIMITER for stored procedures

Usually, a stored procedure contains multiple statements separated by semicolons (;).

To compile the entire stored procedure as a single compound statement, you must temporarily change the delimiter from the semicolon (;) to another delimiter such as $$ or //:

DELIMITER $$

CREATE PROCEDURE CreatePersonTable()
BEGIN
-- drop persons tableDROP TABLE IF EXISTS persons;

-- create persons tableCREATE TABLE persons(
        id INT AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(255) NOT NULL,
        last_name VARCHAR(255) NOT NULL
    );

-- insert data into the persons tableINSERT INTO persons(first_name, last_name)
    VALUES('John','Doe'),
		  ('Jane','Doe');

-- retrieve data from the persons tableSELECT id, first_name, last_name
    FROM persons;
END $$

DELIMITER ;

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 the semicolon (;) in the body of the stored procedure and $$ after the END keyword to end the stored procedure.
• Third, revert to the default delimiter(;).

Summary

• Use the DELIMITER command to change the default delimiter (;) to another of your choice.