When you first learn MySQL, you may not understand the real purpose of delimiters. Delimiters appear in many places in MySQL, such as stored procedures, triggers, functions, etc. People who have learned Oracle will find it very strange and puzzled to learn MySQL. In fact, it tells the MySQL interpreter whether the command has ended and whether MySQL can execute it. By default, the delimiter is a semicolon (;). In the command line client, if a line of commands ends with a semicolon, mysql will execute the command after pressing Enter. If you enter the following statement Then press Enter, and MySQL will execute the statement immediately. But sometimes, you don't want MySQL to do this. You may enter more statements and the statements may contain semicolons. If you try to enter the following statement in the command line client mysql> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT) mysql> RETURNS varchar(255) mysql> BEGIN mysql> IF ISNULL(S) THEN mysql> <strong> RETURN ''; </strong> mysql> ELSE IF N < 15 THEN mysql> RETURN LEFT(S, N); mysql> ELSE mysql> IF CHAR_LENGTH(S) <= N THEN mysql> RETURN S; mysql> ELSE mysql> RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5)); mysql> END IF; mysql> END IF; mysql> END; By default, it is not possible to wait until the user has entered all of the statements before executing the entire statement. Because mysql will automatically execute once it encounters a semicolon. That is, when the statement RETURN ''; is executed, the MySQL interpreter will execute. In this case, you need to replace the delimiter with other symbols in advance, such as // or $$. mysql> delimiter // mysql> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT) mysql> RETURNS varchar(255) mysql> BEGIN mysql> IF ISNULL(S) THEN mysql> RETURN ''; mysql> ELSE IF N < 15 THEN mysql> RETURN LEFT(S, N); mysql> ELSE mysql> IF CHAR_LENGTH(S) <= N THEN mysql> RETURN S; mysql> ELSE mysql> RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5)); mysql> END IF; mysql> END IF; mysql> END;// In this way, the MySQL interpreter will execute this statement only when // appears. Besides. In the MySQL storage process, one thing to note is: drop PROCEDURE if EXISTS proc_while_test; delimiter;; CREATE DEFINER = root@localhost PROCEDURE proc_while_test(IN n int) BEGIN DECLARE i int; DECLARE s int; SET i = 0; SET s = 0; WHILE i <= n DO SET s = s + i; set i++; set i = i + 1; end WHILE; select s; end ;; delimiter ; In the above example, set i++; will cause the entire while loop to report an error. In MySQL stored procedures, i++ is not allowed to be written like this. It needs to be written in the form of i=i+1. Supplement: Let's take a look at the role of delimiter in Mysql 1. delimiter delimiter is the MySQL delimiter. The default delimiter in the MySQL client is a semicolon (;). 2. Delimiter usage In the previous article, there is an example of setting up a MySQL trigger. mysql> delimiter // mysql> create trigger upd_check before update on account -> for each row -> begin -> if new.amount < 0 then -> set new.amount=0; -> elseif new.amount > 100 then -> set new.amount = 100; -> end if; -> end; -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter; The above is, first set the separator to //, The statement is not executed as a whole until the next // is encountered. After execution, the last line, delimiter; resets the MySQL delimiter to a semicolon. If not modified, all separators in this session will be based on //. Summarize The above is a detailed explanation of the role of delimiter in Mysql. I hope it will be helpful to everyone. If you have any questions, please leave me a message and I will reply to you in time! You may also be interested in:
|
<<: js to implement web calculator
>>: Detailed tutorial on installation and configuration of nginx under Centos7
Table of contents nonsense Functions implemented ...
1. Use the df command to view the overall disk us...
Rem layout adaptation The styles in Vant use px a...
FTP is mainly used for file transfer, and is gene...
Preface This article mainly introduces the releva...
Regarding the nginx panic problem, we first need ...
This article shares the specific code of JavaScri...
Table of contents Preface Global parameter persis...
1. Uninstalling MySQL 5.7 1.1查看yum是否安裝過mysql cd y...
This article mainly introduces the layout method ...
Learned ConcurrentHashMap but don’t know how to a...
Table of contents 1. Introduction 2. Main text 2....
<br />In the field of network design, resear...
Big data continues to heat up, and if you are not...
sort Sort the contents of a text file Usage: sort...