問題描述

我經常看到人們使用分隔符.我試圖自己找出什么是分隔符以及它們的目的是什么.經過 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 會導致它被發送到服務器，服務器會報告語法錯誤.例如，使用 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 中的分隔符的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！