How Do Delimiters Work in MySQL and When Should You Use Custom Delimiters?

Delimiter in MySQL

In MySQL, delimiters refer to characters that define the boundaries of SQL statements. By default, MySQL uses the semicolon (;) as the delimiter, but users can specify custom delimiters for specific scenarios.

The role of delimiter

When defining multiple statements in a SQL script, such as creating stored procedures, functions and triggers, delimiters play a vital role. When using the default semicolon as the delimiter, MySQL treats each statement as a separate query and executes them separately.

In this case, a custom separator becomes necessary to signify the end of the entire script or process. This ensures that MySQL recognizes and executes the complete script as a unit, rather than processing individual statements separately.

Custom separator

Developers can define any unique character or sequence of characters as a custom delimiter using the DELIMITER keyword. For example, $$ or // can be used as delimiters. Once defined, all statements in this script must end with a custom delimiter instead of the default;.

Example

The following code snippet demonstrates how to use the custom delimiter $$ to define a stored procedure:

<code class="language-sql">DELIMITER $$

/* 删除现有过程 */
DROP PROCEDURE my_procedure$$

/* 创建新过程 */
CREATE PROCEDURE my_procedure ()
BEGIN
  /* 过程中的语句以;结尾 */
  CREATE TABLE table1 (col1 INT, col2 INT);
  INSERT INTO table1 ...;
END$$

/* 将分隔符重置为默认的; */
DELIMITER ;</code>

Note on restrictions

It should be noted that the DELIMITER keyword is mainly used in the MySQL command line client. Attempting to use it via the programming language API or other clients may result in syntax errors. For example, PHPMyAdmin provides its methods for specifying non-default delimiters.

The above is the detailed content of How Do Delimiters Work in MySQL and When Should You Use Custom Delimiters?. For more information, please follow other related articles on the PHP Chinese website!