sqlmysqldatabasesyntaxdelimiter

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?


Solution

  • 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