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

js to achieve waterfall flow layout (infinite loading)

This article example shares the specific code of ...

Detailed explanation of MySQL data rows and row overflow mechanism

1. What are the formats of lines? You can see you...

Analysis and solutions to problems encountered in the use of label tags

I used the label tag when I was doing something re...

You may not know these things about Mysql auto-increment id

Introduction: When using MySQL to create a table,...

Solution to the problem of var in for loop

Preface var is a way to declare variables in ES5....

Solution to the problem of MySQL thread in Opening tables

Problem Description Recently, there was a MySQL5....

Analysis of the principle of Rabbitmq heartbea heartbeat detection mechanism

Preface When using RabbitMQ, if there is no traff...

Common pitfalls of using React Hooks

React Hooks is a new feature introduced in React ...

Tutorial on processing static resources in Tomcat

Preface All requests in Tomcat are handled by Ser...

Detailed example of database operation object model in Spring jdbc

Detailed example of database operation object mod...

React tsx generates random verification code

React tsx generates a random verification code fo...

Delete the image operation of none in docker images

Since I usually use the docker build command to g...