Reno Reno - 5 months ago 11
SQL Question

Why did this error happened when created EVENT with compound statement?

From this SO question. I got confused with

DELIMITER
. And I also tried something like following:

CREATE EVENT test
ON SCHEDULE EVERY 2 MINUTE
DO
BEGIN
SELECT 1;
SELECT 2;
END


This got me error like mentioned question:


Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5.


And if I ran the sql following:

DELIMITER $$
CREATE EVENT test
ON SCHEDULE EVERY 2 MINUTE
DO
BEGIN
SELECT 1;
SELECT 2;
END $$
DELIMITER;


This worked and created a new
EVENT
sucessfully.

The only difference between these two sql is the last one used
DELIMITER
, so my question is why
DELIMITER
works here. Can anyone explain to me?

Any help is appreciated and thanks in advance.

Answer

It is real trivial, but hard to explain.

The delimiter out of the box is a ; ... That is how mysql knows the statement has ended. But things like stored procedures, events, functions have many statements in them. It needs an outer wrapper for the whole thing to know where it begins and where it ends.

So, you reset the delimiter at the beginning to something bizarre, like $$, put that $$ right after the END, then reset back to factory so to speak to ;

You are going to come up with Error 1064 all the time without it for the creation of stored procedures, events, functions, triggers. And you will burn a lot of time chasing your tail looking for the syntax error that is not there. Well, quite often. When in reality it lacks the DELIMITER wrapper.