Detailed explanation of the definition and function of delimiter in MySQL

Detailed explanation of the definition and function of delimiter in MySQL

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

mysql> select * from test_table;

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 (;).
If you input more statements at one time and there are semicolons in the middle of the statements, you need to specify a special separator.

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:
  • What is the role of MySql delimiter

<<:  js to implement web calculator

>>:  Detailed tutorial on installation and configuration of nginx under Centos7

Recommend

Implementation of Vue top tags browsing history

Table of contents nonsense Functions implemented ...

How to check disk usage in Linux

1. Use the df command to view the overall disk us...

Vant+postcss-pxtorem implements browser adaptation function

Rem layout adaptation The styles in Vant use px a...

How to set up FTP server in CentOS7

FTP is mainly used for file transfer, and is gene...

Mysql solution to improve the efficiency of copying large data tables

Preface This article mainly introduces the releva...

Detailed explanation of the solution to the nginx panic problem

Regarding the nginx panic problem, we first need ...

Detailed explanation of global parameter persistence in MySQL 8 new features

Table of contents Preface Global parameter persis...

CentOS7.x uninstall and install MySQL5.7 operation process and encoding format modification method

1. Uninstalling MySQL 5.7 1.1查看yum是否安裝過mysql cd y...

Flex layout realizes the layout mode of upper and lower fixed and middle sliding

This article mainly introduces the layout method ...

An example of how Tomcat manages Session

Learned ConcurrentHashMap but don’t know how to a...

Concat() of combined fields in MySQL

Table of contents 1. Introduction 2. Main text 2....

Detailed explanation of Linux text processing command sort

sort Sort the contents of a text file Usage: sort...