What is the DELIMITER in MySQL and what it’s used for.

\"Delimiter

The DELIMITER command is used to change the standard delimiter of MySQL commands. The standard command delimiter is the \’;\’ character that defines the end of the query.

The DELIMITER command is used to change the standard delimiter of MySQL commands. The standard command delimiter is the \’;\’ character that defines the end of the query.

Script example:

 

Now let’s have a look at the following script. It consists of several INSERT queries, procedure creating and several more INSERT queries.

 

When executing a script, MySQL client parses queries by delimiting them by \’;\’ and sends them to a server.

If we execute the script in whole, we get the syntax error – \’ERROR 1064 (42000): You have an error in your SQL syntax;…\’. This is because the MySQL client has extracted the following command and tried to execute it.

 

The point is that some objects in MySQL may contain executable code in the BEGINEND clause and commands in the code are delimited by the \’;\’ character; such objects may include: stored procedures and functions, triggers, and events.

In this case, somehow we have to extract the create procedure command in whole. There is the DELIMITER client side command that allows you to set its conditional query delimiter. Any character or string can be set as a delimiter. The most common are $$ and //. In our example the ‘$$’ string is used.

Below is the script with using the DELIMITER command –

 

Now our script will be executed with no errors and the create procedure command will succeed. It\’s also important to note that the DELIMITER command may be set only once for the whole set of commands.

 

The example –

Leave a Reply

Your email address will not be published. Required fields are marked *