MySQL 中的分隔符

Delimiters in MySQL

提问by System.Data

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?

我经常看到人们使用分隔符。我试图自己找出什么是分隔符以及它们的目的是什么。经过 20 分钟的谷歌搜索，我找不到让我满意的答案。所以，我现在的问题是：什么是分隔符，我应该什么时候使用它们？

回答by Michael Berkowski

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 mysqlclient, the client can tell where the entire procedure ends and execute it as a unit rather than executing the individual statements inside.

;在定义函数、存储过程和触发器时通常使用除默认值之外的分隔符 ，其中必须定义多个语句。您定义了一个不同的分隔符，例如$$用于定义整个过程的结尾，但在其中，各个语句都以;. 这样，当代码在mysql客户端运行时，客户端可以知道整个过程在哪里结束并作为一个单元执行，而不是执行内部的单个语句。

Note that the DELIMITERkeyword is a function of the command line mysqlclient (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关键字只是命令行mysql客户端（和一些其他客户端）的函数，而不是常规 MySQL 语言功能。如果您尝试通过编程语言 API 将其传递给 MySQL，它将无法工作。PHPMyAdmin 等其他一些客户端有其他方法来指定非默认分隔符。

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 DELIMITERwith 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:

尝试DELIMITER与不支持它的客户端一起使用会导致它被发送到服务器，这将报告语法错误。例如，使用 PHP 和 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

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的“DELIMITER $$”附近使用的正确语法

回答by Pradeep Singh

The DELIMITER statement changes the standard delimiter which is semicolon ( ;) to another. The delimiter is changed from the semicolon( ;) to double-slashes //.

DELIMITER 语句将分号 (;) 的标准分隔符更改为另一个。分隔符从分号 (;) 更改为双斜杠 //。

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 工具一次解释每个语句。

回答by Rizwan Ahmed

When you create a stored routine that has a BEGIN...ENDblock, statements within the block are terminated by semicolon (;). But the CREATE PROCEDUREstatement 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.

创建具有BEGIN...END块的存储例程时，块内的语句以分号终止(;)。但是CREATE PROCEDURE语句也需要一个终止符。因此，例程主体内的分号是终止CREATE 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 PROCEDUREstatement.

解决歧义的方法是声明一个不同的字符串（它不能出现在过程体中），MySQL 客户端将其识别为CREATE PROCEDURE语句的真正终止符。

回答by Optimizer

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.

您定义一个 DELIMITER 来告诉 mysql 客户端将语句、函数、存储过程或触发器视为一个完整的语句。通常在 .sql 文件中，您可以设置不同的 DELIMITER，如 $$。DELIMITER 命令用于更改 MySQL 命令的标准分隔符（即 ;）。由于例程（函数、存储过程或触发器）中的语句以分号 (;) 结尾，因此我们使用 DELIMITER 将它们视为复合语句。如果在同一文件或命令行中使用不同的例程时未定义，则会出现语法错误。

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.

请注意，您可以使用各种非保留字符来制作您自己的自定义分隔符。您应该避免使用反斜杠 (\) 字符，因为这是 MySQL 的转义字符。

DELIMITER isn't really a MySQL language command, it's a client command.

DELIMITER 并不是真正的 MySQL 语言命令，它是一个客户端命令。

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 ;

回答by Kaumadie Kariyawasam

The delimiter is the character or string of characters that you'll use to tell the MySQL client that you've finished typing in an Sql statement.

分隔符是用于告诉 MySQL 客户端您已完成 Sql 语句的输入的字符或字符串。