What is the purpose of the delimiter in MySQL?

In MySQL, the delimiter is used to specify the separator for SQL statements. By default, MySQL uses the semicolon “;” as the statement delimiter. However, when we need to use multiple statements in objects like stored procedures, functions, triggers, etc., the semicolon can cause syntax errors.

Therefore, we can use the delimiter statement to change the separator. By setting the delimiter to another character (such as $$, //, etc.), we can use multiple statements in objects like stored procedures, functions, triggers, etc. After using a custom separator, it is necessary to use the delimiter ; statement to change the separator back to a semicolon. This way, MySQL can correctly parse and execute objects containing multiple statements.

Here is an example demonstrating how to use a delimiter to define a stored procedure.

delimiter $$
create procedure myProcedure()
begin
    -- 存储过程的第一个语句
    select * from table1;
    
    -- 存储过程的第二个语句
    select * from table2;
end $$
delimiter ;

In the above example, we set the delimiter to $$ so that the two statements in the stored procedure are not incorrectly parsed as separate statements. Finally, we change the delimiter back to a semicolon.