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;Code language: SQL (Structured Query Language) (sql)

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_characterCode language: SQL (Structured Query Language) (sql)

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 //Code language: SQL (Structured Query Language) (sql)

After you change 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 revert to the default delimiter, which is a semicolon (;), you use the following statement:

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

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 table 
    DROP TABLE IF EXISTS persons;
    
    -- create persons table
    CREATE 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 table
    INSERT INTO persons(first_name, last_name)
    VALUES('John','Doe'),
		  ('Jane','Doe');
	
    -- retrieve data from the persons table
    SELECT id, first_name, last_name 
    FROM persons;
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 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.