Today I Learned

hashrocket A Hashrocket project

Change the delimiter in mysql for procedures

Creating procedures in mysql has a weird quirk. When you terminate a statement within the procedure with a ; the entire create procedure statement gets executed up until the semi-colon. This causes a syntax error.

-> create procedure thing()
-> begin
-> set @x = 0;
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 3

To get past this, you need to declare a different delimiter so that mysql know when you are ready to execute the entire create procedure statement. To change the delimiter to //, run:

delimiter //

Now you can execute the create procedure statement by putting // after end:

create procedure thing()
begin
  set @x = 0;
end //
See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.