Delimiter in MySQL

When learning MySQL, you may not understand the real purpose of delimiter, delimiter in many places in MySQL, such as stored procedures, triggers, functions and so on.

Anyone who has learned Oracle will find it strange to learn MySQL.

In fact, just tell the MySQL interpreter if the command is over and MySQL can execute it.
By default, delimiter is a semicolon (;) .

In the command-line client, if a single line of commands ends with a semicolon,
Then after the carriage return, MySQL will execute the command. such as entering the following statement
Mysql> select * from test_table;
Then enter, then MySQL will execute the statement immediately.

But sometimes, you don't want MySQL to do this. More statements are entered for the possible, and the statement contains a semicolon.
If you attempt to enter the following statement in the command line client

mysql> CREATE FUNCTION ' SHORTEN ' (S VARCHAR (255), N INT) mysql>     RETURNS VARCHAR (255) mysql> beginmysql> IF ISNULL (S) thenmysql>    RETURN '; mysql> ELSEIF n<15 thenmysql>     RETURN left (S, N);mysql> elsemysql>     IF char_length (s) <=n thenmysql>    RETURN S; mysql>     elsemysql>    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 these statements before executing the entire statement.
Since MySQL encounters a semicolon, it will be executed automatically.
That is, in the statement RETURN '; , the MySQL interpreter is going to execute.
In this case, the delimiter must be replaced with other symbols such as//or $$ in advance.

Mysql> delimiter//mysql> CREATE FUNCTION ' SHORTEN ' (S VARCHAR (255), N INT) mysql>     RETURNS VARCHAR (255) MySQL > beginmysql> IF ISNULL (s) thenmysql>    return ';mysql> ELSEIF n<15 thenmysql>     return left (s, N );mysql> elsemysql>     IF char_length (S) <=n thenmysql>    RETURN s;mysql>     elsemysql>    RETURN CONCAT (Left (s, N-10), ' ... ', right (s, 5));mysql>     end If;mysql> End if;mysql> end;//

This allows the MySQL interpreter to execute this statement only when // appears.

Besides. Another thing to be aware of in MySQL's stored procedures is:

Drop PROCEDURE if EXISTS proc_while_test;delimiter;; CREATE definer = [email protected] PROCEDURE proc_while_test (in n int) BEGIN DECLARE i int;declare s int;  SET i = 0; SET s = 0; While I <= n doset s = s + i;
　　　　　　　　　set I++;set i = i + 1; End While;select s;end;; delimiter;

In the example above, set i++, which causes an error in the entire while loop. In MySQL's stored procedure, i++ is not allowed to write in this way. This form of i=i+1 needs to be written.

Delimiter in MySQL