Roles of delimiter in MySQL Databases

Source: Internet
Author: User
Tags mysql gui

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.

 
 
  1. 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:

 
 
  1. MySQL> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT)  
  2. MySQL> RETURNS varchar(255)  
  3. MySQL> BEGIN  
  4. MySQL> IF ISNULL(S) THEN  
  5. MySQL> RETURN '';  
  6. MySQL> ELSEIF N<15 THEN  
  7. MySQL> RETURN LEFT(S, N);  
  8. MySQL> ELSE  
  9. MySQL> IF CHAR_LENGTH(S) <=N THEN  
  10. MySQL> RETURN S;  
  11. MySQL> ELSE  
  12. MySQL> RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5));  
  13. MySQL> END IF;  
  14. MySQL> END IF;  
  15. 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 $.

 
 
  1. MySQL> delimiter //  
  2. MySQL> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT)  
  3. MySQL> RETURNS varchar(255)  
  4. MySQL> BEGIN  
  5. MySQL> IF ISNULL(S) THEN  
  6. MySQL> RETURN '';  
  7. MySQL> ELSEIF N<15 THEN  
  8. MySQL> RETURN LEFT(S, N);  
  9. MySQL> ELSE  
  10. MySQL> IF CHAR_LENGTH(S) <=N THEN  
  11. MySQL> RETURN S;  
  12. MySQL> ELSE  
  13. MySQL> RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5));  
  14. MySQL> END IF;  
  15. MySQL> END IF;  
  16. MySQL> END;//  

In this way, the MySQL interpreter will execute this statement only when // appears.

Example:

 
 
  1. MySQL> delimiter //   
  2. MySQL> CREATE PROCEDURE simpleproc (OUT param1 INT)   
  3. -> BEGIN   
  4. -> SELECT COUNT(*) INTO param1 FROM t;   
  5. -> END;   
  6. -> //   
  7. Query OK, 0 rows affected (0.00 sec)   
  8. MySQL> delimiter ;   
  9. MySQL> CALL simpleproc(@a);   
  10. Query OK, 0 rows affected (0.00 sec)   
  11. MySQL> SELECT @a;   
  12. +------+   
  13. | @a |   
  14. +------+   
  15. | 3 |   
  16. +------+   
  17. 1 row in set (0.00 sec)   

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

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

 
 
  1. drop procedure if exists pr_stat_agent;  
  2. -- call pr_stat_agent ('2008-07-17', '2008-07-18')  
  3. create procedure pr_stat_agent  
  4. (  
  5. pi_date_from date  
  6. ,pi_date_to date  
  7. )  
  8. begin  
  9. -- check input  
  10. if (pi_date_from is null) then  
  11. set pi_date_from = current_date();  
  12. end if;  
  13. if (pi_date_to is null) then  
  14. set pi_date_to = pi_date_from;  
  15. end if;  
  16. set pi_date_to = date_add(pi_date_from, interval 1 day);  
  17. -- stat  
  18. select agent, count(*) as cnt  
  19. from apache_log  
  20. where request_time >= pi_date_from  
  21. and request_time < pi_date_to 
  22. group by agent  
  23. order by cnt desc;  
  24. 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 "//"

 
 
  1. drop procedure if exists pr_stat_agent //  
  2. -- call pr_stat_agent ('2008-07-17', '2008-07-18')  
  3. create procedure pr_stat_agent  
  4. (  
  5. pi_date_from date  
  6. ,pi_date_to date  
  7. )  
  8. begin  
  9. -- check input  
  10. if (pi_date_from is null) then  
  11. set pi_date_from = current_date();  
  12. end if;  
  13. if (pi_date_to is null) then  
  14. set pi_date_to = pi_date_from;  
  15. end if;  
  16. set pi_date_to = date_add(pi_date_from, interval 1 day);  
  17. -- stat  
  18. select agent, count(*) as cnt  
  19. from apache_log  
  20. where request_time >= pi_date_from  
  21. and request_time < pi_date_to 
  22. group by agent  
  23. order by cnt desc;  
  24. end; //  
  25. delimiter ;  

Change back to the default MySQL delimiter: ";"

Of course, the MySQL delimiter symbol can be set freely. You can use "/" or "$. However, "//" and "$" are commonly used in MySQL database stored procedures ". 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 "//"

 
 
  1. MySQL> 
  2. MySQL> drop procedure if exists pr_stat_agent //  
  3. -> 
  4. -> -- call pr_stat_agent ('2008-07-17', '2008-07-18')  
  5. -> 
  6. -> create procedure pr_stat_agent  
  7. -> (  
  8. -> pi_date_from date  
  9. -> ,pi_date_to date  
  10. -> )  
  11. -> begin  
  12. -> -- check input  
  13. -> if (pi_date_from is null) then  
  14. -> set pi_date_from = current_date();  
  15. -> end if;  
  16. -> 
  17. -> if (pi_date_to is null) then  
  18. -> set pi_date_to = pi_date_from;  
  19. -> end if;  
  20. -> 
  21. -> set pi_date_to = date_add(pi_date_from, interval 1 day);  
  22. -> 
  23. -> -- stat  
  24. -> select agent, count(*) as cnt  
  25. -> from apache_log  
  26. -> where request_time >= pi_date_from  
  27. -> and request_time < pi_date_to 
  28. -> group by agent  
  29. -> order by cnt desc;  
  30. -> end; //  
  31. -> 
  32. -> delimiter ; 

Change back to the default MySQL delimiter: ";"

 
 
  1. -> //  
  2. -> //  
  3. -> //  
  4. -> ;  
  5. -> ;  
  6. -> 

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

 
 
  1. MySQL> 
  2. MySQL> drop procedure if exists pr_stat_agent //  
  3. Query OK, 0 rows affected (0.00 sec)  
  4. MySQL> 
  5. MySQL> -- call pr_stat_agent ('2008-07-17', '2008-07-18')  
  6. MySQL> 
  7. MySQL> create procedure pr_stat_agent  
  8. -> (  
  9. -> pi_date_from date  
  10. -> ,pi_date_to date  
  11. -> )  
  12. -> begin  
  13. -> -- check input  
  14. -> if (pi_date_from is null) then  
  15. -> set pi_date_from = current_date();  
  16. -> end if;  
  17. -> 
  18. -> if (pi_date_to is null) then  
  19. -> set pi_date_to = pi_date_from;  
  20. -> end if;  
  21. -> 
  22. -> set pi_date_to = date_add(pi_date_from, interval 1 day);  
  23. -> 
  24. -> -- stat  
  25. -> select agent, count(*) as cnt  
  26. -> from apache_log  
  27. -> where request_time >= pi_date_from  
  28. -> and request_time < pi_date_to 
  29. -> group by agent  
  30. -> order by cnt desc;  
  31. -> end; //  
  32. Query OK, 0 rows affected (0.00 sec)  
  33. MySQL> 
  34. MySQL> delimiter ;   

Do not mark "//" at the end

 
 
  1. 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.

 
 
  1. MySQL> source d:\pr_stat_agent.sql  
  2. Query OK, 0 rows affected (0.00 sec)  
  3. Query OK, 0 rows affected (0.00 sec)  

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

 
 
  1. MySQL> \. d:\pr_stat_agent.sql  
  2. Query OK, 0 rows affected (0.00 sec)  
  3. Query OK, 0 rows affected (0.00 sec)  

Finally, we can see that the client tools of MySQL databases are different in some places.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.