When the MySQL delimiter error occur?

The MySQL delimiter occurs when you are using a pipe delimiter(|) with semicolon (;) and using MySQL version lower than 8.0.12.

MySQL treats the pipe (|) as one delimiter and semicolon (;) is another delimiter. Therefore, do not confuse the MySQL delimiter with pipe as well as semicolon.

Note: Here, we are using MySQL version 8.0.12. The pipe delimiter works fine with semicolon. If you are using version lower than 8.0.12, then this leads to a delimiter error.

Here is the working of MySQL delimiter:

mysql> delimiter |;
mysql> create procedure getSumOfTwoNumbers()
   -> begin
   -> select 2+3 as TotalValue;
   -> end ;
   -> |;
Query OK, 0 rows affected (0.17 sec)
mysql> delimiter ;

Now call the stored procedure using call command. The query is as follows:

mysql> call getSumOfTwoNumbers();

The following is the output:

+------------+
| TotalValue |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

Do not achieve the above pipe delimiter with semicolon because in MySQL semicolon is default delimiter which tells the end of statement in MySQL. Therefore, the semicolon can be used to get back from the MySQL stored procedure when you are writing multiple statement.

Here is the example of delimiter change.

First, use the pipe delimiter (|). If you want to set delimiter to pipe, use the following query.

mysql> DELIMITER |

The query to create a stored procedure is as follows:

mysql> DELIMITER |
mysql> create procedure Demo()
   -> begin
   -> select 2*3 as TotalMultiplication;
   -> end ;
   -> |
Query OK, 0 rows affected (0.12 sec)

Now change the delimiter with semicolon. If you want to set delimiter to semicolon, use the following query.

The query is as follows:

mysql> delimiter ;