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

User · Answer

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 ;

User · Answer

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

User · Answer

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- gt query  DELIMITER       echo  mysqli- gt 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

User · Answer

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

[mysql] Delimiters in MySQL

Example

Examples related to mysql

Examples related to delimiter