MySQL中的分隔符 [英] Delimiters in MySQL
问题描述
我经常看到人们在使用定界符.我试图找出什么是分隔符,以及它们的目的是什么.谷歌搜索20分钟后,我找不到能满足我要求的答案.所以,我现在的问题是:什么是分隔符,什么时候应该使用它们?
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?
推荐答案
在定义函数,存储过程和触发器(其中必须定义多个语句)时,通常使用除默认;
以外的分隔符.您定义了一个不同的定界符,如$$
,用于定义整个过程的结尾,但是在其中,每个单独的语句均以;
终止.这样,当代码在mysql
客户端中运行时,客户端可以知道整个过程在何处结束并作为一个单元执行,而不是在其中执行各个语句.
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.
请注意,DELIMITER
关键字仅是命令行mysql
客户端(和某些其他客户端)的功能,而不是常规的MySQL语言功能.如果您尝试将其通过编程语言API传递给MySQL,它将无法正常工作.其他一些客户端(例如PHPMyAdmin)具有其他方法来指定非默认定界符.
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.
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 ;
尝试在不支持DELIMITER
的客户端上使用DELIMITER
,这将导致它被发送到服务器,服务器将报告语法错误.例如,使用PHP和MySQLi:
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;
错误:
您的SQL语法有错误;检查相应的手册 您的MySQL服务器版本以获取正确的语法,以在第1行的"DELIMITER $$"附近使用
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
这篇关于MySQL中的分隔符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!