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.
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