- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 ;
- Why does the following error occur in MySQL: ERROR 1062 (23000): Duplicate entry?
- What is a lunar eclipse? When does it occur?
- How to update the current delimiter in MySQL?
- How to determine the current delimiter in MySQL?
- Create a stored procedure with delimiter in MySQL
- MySQL query to return a substring after delimiter?
- Can we know the last MySQL error?
- MySQL throws an error when the table name “match” is not surrounded by single quotes?
- What does DELIMITER // do in a Trigger in MySQL?
- Execute a script when an error occurs in HTML?
- Resolve the MySQL error 'TYPE=MyISAM'?
- Which punctuation character can be used as the delimiter between MySQL date parts?
- Which punctuation character can be used as the delimiter between MySQL time parts?
- How to correctly use DELIMITER in a MySQL stored procedure?
- Fix MySQL Database Error #1064?