MySQL in delimiter

This command doesn't have anything to do with stored procedures.
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> 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 not possible to wait until the user has finished entering all 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, you need to change the delimiter to 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; //
The MySQL interpreter executes this statement only after the // appears

Example:

mysql> delimiter// 

mysql> CREATE PROCEDURE simpleproc (out param1 INT)  
-begin < Br>-> SELECT COUNT (*) to param1 from t; 
, end; 
,// 
Query OK, 0 rows affected (0.0 0 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)  

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

Compiled a MySQL stored procedure for the user agent of the statistics website. This is the SQL code below.

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 can run smoothly under EMS SQL Manager 2005 for MySQL this MySQL graphics client. But in SQLyog MySQL GUI v5.02 This client will go wrong. The last reason to find out is the problem of not setting delimiter. 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 so, it will be a problem if you submit it to MySQL every time. 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   &nbSp; 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 default  mysql delimiter: ";"

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 =&nbSp;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 default  mysql  delimiter: ";"     -> //    -> //    -> / /    -> ;    -> ;    ->

> It's weird! Finally found the problem, the MySQL command line run "delimiter//;" Then the MySQL delimiter is actually "//;" Instead of "//" as we expected. In fact, just run the command "delimiter//" is OK.

mysql> delimiter //     --  at the end do 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&NBSP;SEC) mysql>mysql> delimiter ;  --  End No sign   "//" 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.sqlQuery OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec)

The abbreviated form of the source directive is: "\."

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

MySQL in delimiter