What is the role of MYSQL delimiter _mysql

After MySQL exports one sql:
DELIMITER $$
DROP TRIGGER IF EXISTS ' Updateegopriceondelete ' $$
CREATE
TRIGGER ' Updateegopriceondelete ' after the DELETE on ' CustomerInfo '
For each ROW BEGIN
DELETE from Egoprice WHERE Customerid=old.customerid;
end$$
DELIMITER;

Where delimiter a terminator is "$$" and then finally defined as ";", MySQL's default terminator is ";".



Detailed Explanation:

In fact, tell the MySQL interpreter, whether the command is over, whether MySQL can be executed.
By default, delimiter is a semicolon;. In a command-line client, if a line of commands ends with a semicolon,
Then, after the carriage return, MySQL will execute the command. If you enter the following statement
Mysql> select * from test_table;
And then enter, then MySQL will execute the statement immediately.

But sometimes, you don't want MySQL to do this. A statement containing a semicolon is included in the statement for which you may enter more.
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 to left (S, N);
Mysql> ELSE
Mysql> IF char_length (S) <=n THEN
mysql> return S;
Mysql> ELSE
Mysql> return CONCAT (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 completed all of these statements and then execute the entire paragraph.
Because MySQL once encountered a semicolon, it will automatically execute.
That is, the MySQL interpreter will execute when the statement return ';
In this case, you need to replace delimiter with other symbols, 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 to left (S, N);
Mysql> ELSE
Mysql> IF char_length (S) <=n THEN
mysql> return S;
Mysql> ELSE
Mysql> return CONCAT (S, N-10), ' ... ', right (s, 5));
Mysql> End IF;
Mysql> End IF;
Mysql> end;//
This is the only time the MySQL interpreter executes this statement when//appears.


Example:

Mysql> delimiter//

Mysql> CREATE PROCEDURE Simpleproc (out param1 INT)
-> BEGIN
-> SELECT COUNT (*) into the param1 from T;
-> end;
->//
Query OK, 0 rows Affected (0.00 sec)

Mysql> delimiter;

Mysql> call Simpleproc (@a);
Query OK, 0 rows Affected (0.00 sec)

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






This code runs under the MySQL 5.0.41-community-nt.

Write a MySQL stored procedure that counts Web site access (user agent). This is the following SQL code.

drop procedure if exists pr_stat_agent;

--Call Pr_stat_agent (' 2008-07-17 ', ' 2008-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 am in the EMS SQL Manager for MySQL this MySQL graphics client can run smoothly. But in SQLyog MySQL GUI v5.02 This client will be wrong. Finally find the reason is not set good delimiter problem. By default, delimiter ";" is used to submit query statements to MySQL. There is a ";" at the end of each SQL statement in the stored procedure, and if it is submitted to MySQL at this time, then of course there will be a problem. So change the MySQL delimiter, the above MySQL stored procedure is programmed this way:

delimiter//; --Change MySQL delimiter to: "//"

drop procedure if exists pr_stat_agent//

--Call Pr_stat_agent (' 2008-07-17 ', ' 2008-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 back to the default MySQL delimiter: ";"

Of course, the MySQL delimiter symbol is free to set, you can use "/" or "$$" and so on. But the more common usage of MySQL stored procedures is "//" and "$$". The above code in SQLyog moved to the MySQL command client (MySQL commands line clients) but could not be executed.

Mysql> delimiter//; --Change MySQL delimiter to: "//"
Mysql>
Mysql> drop procedure if exists pr_stat_agent//
->
->--Call Pr_stat_agent (' 2008-07-17 ', ' 2008-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 back to the default MySQL delimiter: ";"
->//
->//
->//
->;
->;
->

It's so weird! Finally found the problem, under the MySQL command line to run "delimiter//;" Then the MySQL delimiter is actually "//;" Rather than the "//" we expected. In fact, just run the command "delimiter//" on the OK.

Mysql> delimiter//-End not sign ";"
Mysql>
Mysql> drop procedure if exists pr_stat_agent//
Query OK, 0 rows Affected (0.00 sec)

Mysql>
Mysql>--Call Pr_stat_agent (' 2008-07-17 ', ' 2008-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; --No sign "//" at the end
Mysql>

Incidentally, we can execute the SQL code in the file in the MySQL database. For example, I put the code for the stored procedure above in the file D:\pr_stat_agent.sql. You can run the following code to establish 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 source directive is abbreviated in the form: "\."

mysql> \. D:\pr_stat_agent.sql
Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Finally, it can be seen that MySQL's client tools are fragmented in some places, each with its own set.