How to Resolve ERROR 1064 (42000): You have an error in your SQL syntax

Create Stored Procedure

Assuming that you already have a block of codes, especially it's a stored procedure (e.g. CREATE FUNCTION, CREATE PROCEDURE, CREATE TRIGGER) to be created like this:

1 CREATE TRIGGER test_trigger BEFORE INSERT ON table_name
2 FOR EACH ROW
3 begin
4     set new.total = new.salary + new.bonus;
5 end;

Delimiter Semicolon

You might get the error in the console when you are trying to create the trigger:

mysql> CREATE TRIGGER test_trigger BEFORE INSERT ON table_name
    -> FOR EACH ROW
    -> begin
    ->     set new.total = new.salary + new.bonus;
ERROR 1064 (42000): 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 4
mysql> end;

You can see MySQL stopped when it saw the first semi-colon, and reported a syntax error. It's a normal behavior for MySQL itself without question, because a semi-colon is usually the termination character of a statement in SQL world by default.

Delimiter Double Slashes

To solve the error, you must explicitly set a delimiter other than a semi-colon to guide MySQL to take all the codes as a whole until the termination delimiter shows. Here we use double slashes to be the delimiter of a block of code.

1 delimiter //
2 CREATE TRIGGER test_trigger BEFORE INSERT ON table_name
3 FOR EACH ROW
4 begin
5     set new.total = new.salary + new.bonus;
6 end//

You can see I declare // as the termination delimiter in Line 1, which will be the termination character throughout my session life time, so MySQL will wait and wait until the delimiter shows in Line 6. Let's try to execute it again.

mysql> delimiter //
mysql> CREATE TRIGGER test_trigger BEFORE INSERT ON table_name
    -> FOR EACH ROW
    -> begin
    ->     set new.total = new.salary + new.bonus;
    -> end//
Query OK, 0 rows affected (0.02 sec)

mysql>

It's pretty flexible for MySQL developers to set the termination delimiter to any other symbols as they like, for instance, "$$".

Go Back to Default

To cancel the customized delimiter and back to the default, just do:

mysql> delimiter ;