MySQL Delimiter – A Complete Guide

In this tutorial, we will learn what a delimiter is, how it is used, how to change it, and how to use it in MySQL queries. It is quite an easy topic to understand; however, we will try to explain each and every point related to the delimiter in this article.

What is a MySQL Delimiter?

In MySQL, we use a semicolon (;) as a default delimiter to separate the statements and execute them separately. The delimiter is, you can think of it as a notation that denotes the end of the statement. For example,

SELECT * FROM table;
INSERT INTO table VALUES(value);Code language: SQL (Structured Query Language) (sql)

In the above examples, the two statements are individual statements and that’s why they end with semicolons. So, the semicolon is the default delimiter in MySQL because we use the semicolon to specify the end of the statement.

Why Do We Need Delimiter?

When you write an individual statement as we mentioned in the above example, you will need only a semicolon at the end of the statement. But what if you want to write a block of statements that works as a single unit? In that case, you will need to change the delimiter.

In MySQL, stored procedures, functions and triggers are the blocks of statements where you need the delimiter other than the default semicolon. The delimiter helps MySQL to acknowledge the group of statements as a single unit or single task. However, the individual statements in the blocks end with semicolons.

When you change the delimiter, you start writing the block of statements and end the block with the same delimiter by assuming the whole block as a single statement. Have a look below to understand it better-

DELIMITER //
CREATE PROCEDURE proc()
BEGIN
DECLARE varA INT;
DECLARE varB INT;
SELECT * FROM table;
END
//Code language: SQL (Structured Query Language) (sql)

Here, you can see, we have changed the delimiter to // and started writing the procedure body. Inside the body, we have written three statements that end with semicolons. In the end, we have completed the procedure body by writing the delimiter //. The delimiter at the end specifies the end of the block statement that should work in one go instead of executing each statement separately.

How To Change the Delimiter?

You can change the delimiter by using the DELIMITER keyword.

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

The delimiter_character must be an unreserved character in MySQL for example, // , $$, ## etc.

Examples-

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

The above statement will change the default delimiter to the //. Note that, if you execute a single SQL statement, you must end that statement with the latest delimiter. Unlike the stored procedure, function or trigger where only the block ends with a delimiter and all statements inside the body end with the default delimiter (semicolon).

Now, you can use the above delimiter as follows-

SELECT * FROM table //
INSERT INTO table VALUES(value) //Code language: SQL (Structured Query Language) (sql)

You can change the delimiter back to the default by using the same syntax.

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

When and Where Should I Use Delimiter?

As mentioned above, you must change the delimiter when you need to write multiple statements that act as a single unit or single task. You will need a delimiter mostly while defining stored procedures, stored functions and triggers.

The delimiter is not a MySQL programming function. It is a command provided by the MySQL Client. You cannot pass it through the programming language API to MySQL. Different MySQL clients have different methods to specify the delimiter. You may refer to docs before proceeding.

Make sure after writing a procedure or function, you change the default delimiter to the semicolon. It is not compulsory, although it is a good practice to use a semicolon as a default delimiter.

Conclusion

In this article, we learned what is a delimiter, why do we need it and how can we use it in our queries. I hope your all doubts regarding the delimiter are clear now. It is easy to understand and you will need it more often when you learn stored procedures, functions, and triggers.