What is the usage of the delimiter in MySQL?

1 year ago
Jackson Davis
2 minutes

In MySQL, DELIMITER is used to define a custom delimiter for commands. By default, MySQL uses a semicolon (;) as the end of a command, but in certain cases, the semicolon may interfere with command execution. Therefore, DELIMITER allows you to customize a delimiter that won’t interfere with commands.

Here is how to use the DELIMITER:

  1. Set the delimiter: Use the DELIMITER statement to set a custom delimiter. For example, DELIMITER // will set the delimiter as //.
  2. Write instructions: Write instructions between custom delimiters. For example, CREATE PROCEDURE myProcedure() BEGIN … END//.
  3. End directive: Use a custom delimiter to end the directive. For example, //.
  4. Restore the default delimiter: Use DELIMITER ; to set the delimiter back to the default semicolon.

Important points to note:

  1. When using custom delimiters, it is important to ensure that the delimiter does not appear in the command, otherwise it will prevent the command from executing.
  2. The DELIMITER statement does not need to end with a delimiter itself, as the beginning and end of the directive will be automatically handled.
  3. Custom delimiters are only effective in the command line terminal and cannot be used in SQL script files.

Here is an example of defining a stored procedure using DELIMITER:

DELIMITER //
CREATE PROCEDURE myProcedure()
BEGIN
    -- 执行的逻辑指令
END//
DELIMITER ;

In the example above, the DELIMITER // command is used to set the delimiter as //, allowing the logic instructions of a stored procedure to be written between BEGIN and END. Finally, the commands are ended with // and the delimiter is restored to the default semicolon using DELIMITER ;.

bannerAds