Roles of delimiter in MySQL database (1)

The following articles mainly describe the role of delimiter In the MySQL database? We generally think that this command has little to do with the stored procedure. Is it true? The following articles will give you relevant knowledge and I hope you will gain some benefits.

In fact, it is to tell the MySQL interpreter whether the command has ended and whether the MySQL database can be executed. By default, delimiter is a semicolon ;. In the command line client, if a line of command ends with a semicolon, press enter and MySQL will execute the 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 database 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(@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.

Write a MySQL database stored procedure that collects Website access statistics on user agent. 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 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.