The most misunderstood character in MySQL's SQL-Dialect

Many MySQL users like to use the MySQL monitor. The MySQL monitor is the executable bin/mysql (or if you are on MS Windows, bin\mysql.exe) and is perhaps better known as the MySQL command-line tool or MySQL command-line client.

The MySQL monitor

Basically, the MySQL monitor is a standard, text-based terminal that connects to a running MySQL Server when it is started. Once the connection is established, the user can enter text via the computer keyboard, which is at some point sent to the server. The server attempts to interpret the received text as SQL, which is then executed, sending the result back to the MySQL monitor. The MySQL monitor then prints the received results on the screen:

mysql> SELECT COUNT(*) FROM world.City;
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.02 sec)

Here we see that immediately after the mysql> prompt, a line of text was entered. In the next few lines we see the result returned by the server. Or, to be really precise, the server returned a resultset consisting of one column and one row having the value 4079. The MySQL monitor is responsible for printing the ASCII art and the padding, printing this in a nice, human readable manner.

Statement terminator

This little dialog may seem monumentally uninteresting, but I hope to show that there really is something here to be learned. Let's focus for a moment on relating this dialog with the general description I gave of the MySQL client/server communication right preceding this example.

I explained that the client sends the text to the server, which interprets it as SQL and returns the result. The 'interesting' thing is this: if the server is the one that interprets text as SQL, how then does the client know when to send the entered text to server?

For many people the answer will be obvious: it is the semi-colon that appears as the last character on the first line of our example, right after the entered text. So the MySQL monitor does not know how to recognize SQL, but it does know how to recognize a semi-colon, and interprets that as a statement-terminator. So, that semi-colon actually never reaches the server - the MySQL monitor just sends the text that appears before the poition where the semi-colon was detected.

The DELIMITER command

We can tell the MySQL monitor to not scan for semi-colons, but for something else entirely. This is done using the DELIMITER command:

mysql> DELIMITER go
mysql> SELECT COUNT(*) FROM world.City
    -> go
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.02 sec)

So here we see that the MySQL monitor is instructed to scan for go rather than the semi-colon. The line that goes like DELIMITER go is not an SQL-statement: it is a command recognized by the MySQL monitor, and it is never sent to the server.

The semi-colon in stored routines

Most MySQL users that have created stored routines and the like will be familiar with this DELIMITER command.

The reason is that in many cases, stored routines are composed of typical procedural constructs such as BEGIN...END compound statements and flow-control statements such as IF...THEN and WHILE...DO. These types of statements can themselves contain a list of statements, and the server requires each of these statements to be terminated (or actually, separated) by the semi-colon.

Obviously, if stored routine statements like these would be entered through the MySQL monitor, it is no use if the MySQL monitor still scans for semi-colons. It will just send text prematurely to the server, which in turn tries to interpret each as a well-formed SQL statement, usually resulting in a frenzy of errors being returned back to the MySQL monitor.

So, this is probably one of the most abundant usages of the DELIMITER command: to allow stored routines to be entered through the MySQL monitor.

The semi-colon outside stored routines

What many MySQL users don't realize is that it is perfectly valid to separate normal, non-procedureal SQL statements with the semi-colon. Like when entering stored routines, the MySQL monitor needs to have its delimiter set to something other than the semi-colon, but after having done so, you can enter a batch of statements to be sent to the server at once:

mysql> DELIMITER go
mysql> SELECT COUNT(*)
    -> FROM world.Country;
    -> SELECT COUNT(*)
    -> FROM world.City;
    -> go
+----------+
| COUNT(*) |
+----------+
|      239 |
+----------+
1 row in set (0.00 sec)

+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

This is not some trick of the MySQL monitor: the batch is sent as a whole, and the results are received as a whole too, all in one network rountrip.