Delimiters in MySQL

Question

I often see people are using Delimiters. I tried myself to find out what are delimiters and what is their purpose. After 20 minutes of googling, I was not able to find an answer which satisfies me. So, my question is now: What are delimiters and when should I use them?

Salto

mysqltutorial.org/mysql-stored-procedure/mysql-delimiter – Devalue

Answer 1

Delimiters other than the default ; are typically used when defining functions, stored procedures, and triggers wherein you must define multiple statements. You define a different delimiter like $$ which is used to define the end of the entire procedure, but inside it, individual statements are each terminated by ;. That way, when the code is run in the mysql client, the client can tell where the entire procedure ends and execute it as a unit rather than executing the individual statements inside.

Note that the DELIMITER keyword is a function of the command line mysql client (and some other clients) only and not a regular MySQL language feature. It won't work if you tried to pass it through a programming language API to MySQL. Some other clients like PHPMyAdmin have other methods to specify a non-default delimiter.

Example:

DELIMITER $$
/* This is a complete statement, not part of the procedure, so use the custom delimiter $$ */
DROP PROCEDURE my_procedure$$

/* Now start the procedure code */
CREATE PROCEDURE my_procedure ()
BEGIN    
  /* Inside the procedure, individual statements terminate with ; */
  CREATE TABLE tablea (
     col1 INT,
     col2 INT
  );

  INSERT INTO tablea
    SELECT * FROM table1;

  CREATE TABLE tableb (
     col1 INT,
     col2 INT
  );
  INSERT INTO tableb
    SELECT * FROM table2;
  
/* whole procedure ends with the custom delimiter */
END$$

/* Finally, reset the delimiter to the default ; */
DELIMITER ;

Attempting to use DELIMITER with a client that doesn't support it will cause it to be sent to the server, which will report a syntax error. For example, using PHP and MySQLi:

$mysqli = new mysqli('localhost', 'user', 'pass', 'test');
$result = $mysqli->query('DELIMITER $$');
echo $mysqli->error;

Errors with:

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 'DELIMITER $$' at line 1

Vicereine

What are scenarios where it makes sense to use a delimiter? I didn't really got it. – Salto

@Salto First sentence of my answer - when defining stored procedures, custom functions, and triggers. Any time multiple statements are executed as a unit on the MySQL command line. – Vicereine

Note that the DELIMITER command doesn't work from a PhpMyAdmin SQL (at least, some versions). Instead, you can set the delimiter in a separate field to the SQL itself. This had me confused for quite some time... :-) – Katelyn

@HighlyIrregular I did have a note about PhpMyAdmin above - perhaps it needs to be called out and clarified more? – Vicereine

@MichaelBerkowski, Do you mean that delimiter isn't a MySQL keyword? – Mollescent

@Mollescent Yes. While DELIMITER is a keyword of the command line mysql client, it is not on the list of MySQL reserved words and not part of MySQL's regular SQL language implementation – Vicereine

@MichaelBerkowski, If MySQL server doesn't recognize the delimiter keyword, how could the mysql client send the server a query to create a stored procedure with multiple statements? – Mollescent

@Mollescent The server recognizes the stored procedure as the CREATE PROCEDURE...BEGIN...END and knows to execute the complete statement between BEGIN/END. But the client uses DELIMITER to inform when it has reached the end of a statement to send to the server. Different clients use different implementations for this. – Vicereine

dev.mysql.com/doc/refman/5.0/en/create-procedure.html "The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself. See Section 18.1, “Defining Stored Programs”." And using DELIMITER in a client that doesn't support it will send it to the server, resulting in a 1064 syntax error near DELIMITER – Vicereine

Great explanation Michael. The DELIMITER keyword has all the aspect of being an unnecessary burden imposed to the users aimed at circumvent the stupidity of MySQL clients being unable to segment the statements by themselves. It doesn't seem difficult to recognize that compound statements terminate with END or END IF and the like. The clients could probably be able to do this by their own. – Boltzmann

Answer 2

The DELIMITER statement changes the standard delimiter which is semicolon ( ;) to another. The delimiter is changed from the semicolon( ;) to double-slashes //.

Why do we have to change the delimiter?

Because we want to pass the stored procedure, custom functions etc. to the server as a whole rather than letting mysql tool to interpret each statement at a time.

Does

Answer 3

When you create a stored routine that has a BEGIN...END block, statements within the block are terminated by semicolon (;). But the CREATE PROCEDURE statement also needs a terminator. So it becomes ambiguous whether the semicolon within the body of the routine terminates CREATE PROCEDURE, or terminates one of the statements within the body of the procedure.

The way to resolve the ambiguity is to declare a distinct string (which must not occur within the body of the procedure) that the MySQL client recognizes as the true terminator for the CREATE PROCEDURE statement.

Notification

This is the best answer because it is telling the correct reason to use DELIMITER in simplest way and without causing any confusion. Thanks – Killough

Answer 4

You define a DELIMITER to tell the mysql client to treat the statements, functions, stored procedures or triggers as an entire statement. Normally in a .sql file you set a different DELIMITER like $$. The DELIMITER command is used to change the standard delimiter of MySQL commands (i.e. ;). As the statements within the routines (functions, stored procedures or triggers) end with a semi-colon (;), to treat them as a compound statement we use DELIMITER. If not defined when using different routines in the same file or command line, it will give syntax error.

Note that you can use a variety of non-reserved characters to make your own custom delimiter. You should avoid the use of the backslash (\) character because that is the escape character for MySQL.

DELIMITER isn't really a MySQL language command, it's a client command.

Example

DELIMITER $$

/*This is treated as a single statement as it ends with $$ */
DROP PROCEDURE IF EXISTS `get_count_for_department`$$

/*This routine is a compound statement. It ends with $$ to let the mysql client know to execute it as a single statement.*/ 
CREATE DEFINER=`student`@`localhost` PROCEDURE `get_count_for_department`(IN the_department VARCHAR(64), OUT the_count INT)
BEGIN
    
    SELECT COUNT(*) INTO the_count FROM employees where department=the_department;

END$$

/*DELIMITER is set to it's default*/
DELIMITER ;

Mathia

Answer 5

In MySQL, a delimiter is one or more characters to run a SQL statement and ; is used as a delimiter by default according to the doc as shown below:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, ...

So, you can run the SQL statement below with ; in MySQL by default:

                           ↓
mysql> SELECT * FROM person;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | David |
+----+-------+

And, you can still run the SQL statement below with ; even if there are some spaces between them:

                               ↓
mysql> SELECT * FROM person    ;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | David |
+----+-------+

And, you can change the default delimiter ; to hello with --delimiter= or --delimiter when you log in. *If you log out, the delimiter hello is changed back to the default delimiter ;:

mysql -u john -p --delimiter=hello

Or:

mysql -u john -p --delimiter hello

Or, you can change the default delimiter ; to hello with delimiter(DELIMITER) and \d. *You must put one or more spaces between delimiter(DELIMITER) and hello otherwise delimiter command does not run and if you use \D, then you get the error ERROR: Unknown command '\D'.:

mysql> delimiter hello

Or:

mysql> DELIMITER hello

Or:

mysql> \d hello

Or:

mysql> \dhello

Or on Windows, you can set the delimiter hello under [mysql] in my.ini as shown below. *My answer explains [mysql] and my answer explains where my.ini is located on Windows:

# "my.ini"

[mysql]
...
delimiter='hello'

Then, you can set the delimiter hello with login by setting my.ini's location to --defaults-file= or --defaults-extra-file= as shown below. *--defaults-file= or --defaults-extra-file= must be the 1st option otherwise there is the error:

mysql --defaults-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' -u john -p 

Or:

mysql --defaults-extra-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' -u john -p

Then, you can run the SQL statement below with hello:

                           ↓↓↓↓↓
mysql> SELECT * FROM personhello
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | David |
+----+-------+

And, you can still run the SQL statement below with hello even if there are some spaces between them:

                               ↓↓↓↓↓
mysql> SELECT * FROM person    hello
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | David |
+----+-------+

And, you can change the delimiter hello to ; as shown below if you want to use ; as a delimiter again. *You must put one or more spaces between delimiter and ; otherwise delimiter command does not run:

delimiter ;

And for example, basically, you need to change the default delimiter ; to something like $$ when creating an event, then after creating an event, you need to change the delimiter $$ back to ; as shown below. *My answer explains events in MySQL and you must select a database when creating an event otherwise there is the error:

mysql> USE apple;
mysql> delimiter $$
    -> CREATE EVENT my_event
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2023-11-08 00:00:00'
    -> DO
    -> BEGIN
    -> SELECT * FROM person;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

But, if you don't change the default delimiter ; to something like $$ when creating an event, then you get the error as shown below:

mysql> USE apple;
mysql> CREATE EVENT my_event
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2023-11-08 00:00:00'
    -> DO
    -> BEGIN
    -> SELECT * FROM person;
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 6

Sacking