Delimiters in MySQL

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?

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

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.

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.

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 ;

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:ProgramDataMySQLMySQL Server 8.0my.ini' -u john -p

Or:

mysql --defaults-extra-file='C:ProgramDataMySQLMySQL Server 8.0my.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

Filed under: Kiến thức lập trình - @ 09:18

Thẻ: sqlmysqldatabasesyntaxdelimiter

Thiết kế website giá rẻ

Danh mục

Delimiters in MySQL

Example:

Example