When does the MySQL delimiter error occur?

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

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

Note: Here, we use MySQL version 8.0.12. Pipe delimiters work well with semicolons. If you are using a version earlier than 8.0.12, this will cause delimiter errors.

Here's how MySQL delimiters work:

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

Now use the call command to call the stored procedure. 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 use a semicolon to implement the above pipe delimiter because in MySQL the semicolon is the default delimiter and it tells the end of the statement in MySQL. So when you write multiple statements, you can use semicolons to return from MySQL stored procedures.

Here is an example of separator changes.

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

mysql> DELIMITER |

The query to create the 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 the delimiter to semicolon, use the following query.

The query is as follows:

mysql> delimiter ;

The above is the detailed content of When does the MySQL delimiter error occur?. For more information, please follow other related articles on the PHP Chinese website!