What is the role of MySql delimiter?

After MYSQL exports an SQL statement:
DELIMITER $
Drop trigger if exists 'updateegopriceondelete' $
CREATE
TRIGGER 'updateegopriceondelete' after delete on 'mermerinfo'
FOR EACH ROW BEGIN
Delete from egoprice WHERE customerId = OLD. customerId;
END $
DELIMITER;

Set the DELIMITER terminator to "$", and then define it as ";". The default terminator of MYSQL is ";".



Explanation:

In fact, it is to tell the mysql interpreter whether the command has ended and whether mysql can be executed.
By default, delimiter is a semicolon ;. In the command line client, if a command line ends with a semicolon,
After you press enter, mysql will execute this command. Enter the following statement.
Mysql> select * from test_table;
Press enter, and MySQL will immediately execute the statement.

But sometimes, you don't want MySQL to do this. The statement contains a semicolon.
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> RETURN '';
Mysql> elseif 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 impossible for the user to execute the entire statement after entering all these statements.
Mysql runs automatically when it encounters a semicolon.
That is, when the statement RETURN '';, the mysql interpreter is executed.
In this case, you need to replace delimiter with another symbol, 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> elseif 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.


Example:

Mysql> delimiter //

Mysql> create procedure simpleproc (OUT param1 INT)
-> BEGIN
-> Select count (*) INTO param1 FROM t;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

Mysql> delimiter;

Mysql> CALL simpleproc (@ );
Query OK, 0 rows affected (0.00 sec)

Mysql> SELECT @;
+ ------ +
| @ A |
+ ------ +
| 3 |
+ ------ +
1 row in set (0.00 sec)






The code in this article runs through MySQL 5.0.41-community-nt.

Compiled a MySQL stored procedure that counts Website access (user agent. Is the following SQL code.

Drop procedure if exists pr_stat_agent;

-- Call pr_stat_agent ('2017-07-17 ', '2017-07-18 ')

Create procedure pr_stat_agent
(
Pi_date_from date
, Pi_date_to date
)
Begin
-- Check input
If (pi_date_from is null) then
Set pi_date_from = current_date ();
End if;

If (pi_date_to is null) then
Set pi_date_to = pi_date_from;
End if;

Set pi_date_to = date_add (pi_date_from, interval 1 day );

-- Stat
Select agent, count (*) as cnt
From apache_log
Where request_time> = pi_date_from
And request_time <pi_date_to
Group by agent
Order by cnt desc;
End;

I can run smoothly on the MySQL graphical client EMS SQL Manager 2005 for MySQL. However, an error occurs on the SQLyog MySQL GUI v5.02 client. The final reason is that delimiter is not properly configured. By default, delimiter ";" is used to submit query statements to MySQL. In the stored procedure, each SQL statement ends with a ";". If ";" is submitted to MySQL at this time, a problem may occur. So I changed MySQL's delimiter. The above MySQL stored procedure was programmed like this:

Delimiter //; -- change MySQL delimiter to "//"

Drop procedure if exists pr_stat_agent //

-- Call pr_stat_agent ('2017-07-17 ', '2017-07-18 ')

Create procedure pr_stat_agent
(
Pi_date_from date
, Pi_date_to date
)
Begin
-- Check input
If (pi_date_from is null) then
Set pi_date_from = current_date ();
End if;

If (pi_date_to is null) then
Set pi_date_to = pi_date_from;
End if;

Set pi_date_to = date_add (pi_date_from, interval 1 day );

-- Stat
Select agent, count (*) as cnt
From apache_log
Where request_time> = pi_date_from
And request_time <pi_date_to
Group by agent
Order by cnt desc;
End ;//

Delimiter; // -- change to the default MySQL delimiter: ";"

Of course, the MySQL delimiter symbol can be set freely. You can use "/" or "$. However, the common usage of MySQL stored procedures is "//" and "$ ". The above code in SQLyog is moved to the MySQL Command Client (MySQL Command Line Client) but cannot be executed.

Mysql> delimiter //; -- change MySQL delimiter to "//"
Mysql>
Mysql> drop procedure if exists pr_stat_agent //
->
-> -- Call pr_stat_agent ('2017-07-17 ', '2017-07-18 ')
->
-> Create procedure pr_stat_agent
-> (
-> Pi_date_from date
->, Pi_date_to date
->)
-> Begin
-> -- Check input
-> If (pi_date_from is null) then
-> Set pi_date_from = current_date ();
-> End if;
->
-> If (pi_date_to is null) then
-> Set pi_date_to = pi_date_from;
-> End if;
->
-> Set pi_date_to = date_add (pi_date_from, interval 1 day );
->
-> -- Stat
-> Select agent, count (*) as cnt
-> From apache_log
-> Where request_time> = pi_date_from
-> And request_time <pi_date_to
-> Group by agent
-> Order by cnt desc;
-> End ;//
->
-> Delimiter; // -- change to the default MySQL delimiter: ";"
-> //
-> //
-> //
->;
->;
->

It's strange! Finally, we finally found the problem. If we run "delimiter //;" on the MySQL command line, MySQL's delimiter is actually "//;" instead of what we expected "/". In fact, you only need to run the command "delimiter.

Mysql> delimiter // -- do not mark ";" at the end
Mysql>
Mysql> drop procedure if exists pr_stat_agent //
Query OK, 0 rows affected (0.00 sec)

Mysql>
Mysql> -- call pr_stat_agent ('2017-07-17 ', '2017-07-18 ')
Mysql>
Mysql> create procedure pr_stat_agent
-> (
-> Pi_date_from date
->, Pi_date_to date
->)
-> Begin
-> -- Check input
-> If (pi_date_from is null) then
-> Set pi_date_from = current_date ();
-> End if;
->
-> If (pi_date_to is null) then
-> Set pi_date_to = pi_date_from;
-> End if;
->
-> Set pi_date_to = date_add (pi_date_from, interval 1 day );
->
-> -- Stat
-> Select agent, count (*) as cnt
-> From apache_log
-> Where request_time> = pi_date_from
-> And request_time <pi_date_to
-> Group by agent
-> Order by cnt desc;
-> End ;//
Query OK, 0 rows affected (0.00 sec)

Mysql>
Mysql> delimiter; -- do not mark "//" at the end
Mysql>

We can execute the SQL code in the file in the MySQL database. For example, I put the stored procedure code in the file d: \ pr_stat_agent. SQL. You can run the following code to create a stored procedure.

Mysql> source d: \ pr_stat_agent. SQL
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The abbreviated form of the source command is "\."

Mysql> \. d: \ pr_stat_agent. SQL
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Finally, we can see that the MySQL client tool is different in some places.