In MySQL, the semicolon (;) is used as a statement delimiter to separate individual SQL statements. However, when writing stored procedures or triggers that contain multiple SQL statements, using the semicolon as a delimiter can cause issues, since the MySQL parser will interpret each semicolon as the end of the entire stored procedure or trigger, instead of just the end of a single SQL statement.
To avoid this issue, MySQL provides the DELIMITER command, which allows you to specify a different delimiter that will be used to separate individual statements within a stored procedure or trigger. The DELIMITER command is used as follows:
DELIMITER //
In this example, we have set the delimiter to //. This means that when we write a stored procedure or trigger, we will use // instead of ; as the statement delimiter.
Here is an example of a stored procedure that uses the DELIMITER command:
DELIMITER //
CREATE PROCEDURE my_proc()
BEGIN
SELECT 'Hello';
SELECT 'World';
END //
DELIMITER ;
In this example, we have set the delimiter to // at the beginning of the stored procedure, and then used // to separate the two SELECT statements within the stored procedure. Finally, we have set the delimiter back to ; at the end of the stored procedure.
Using DELIMITER allows us to use semicolons within the stored procedure without causing issues with the MySQL parser. When the stored procedure is executed, the // delimiter will be used to separate the individual statements within the procedure.
Note that you can use any character or string as the delimiter, as long as it is not used within the stored procedure or trigger itself.
