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!