Overview of the role of delimiter in MySQL database

Source: Internet
Author: User
Tags mysql command line mysql gui

The following article is mainly to describe what is the role of delimiter in MySQL database? We generally think that this command is not related to stored procedures, is this the case? The following articles will give you the relevant knowledge, hope you will have something to gain.

In fact, just tell the MySQL interpreter if the command is over and the MySQL database is ready to execute. By default, delimiter is a semicolon;. In the command-line client, if a single line of commands ends with a semicolon, the command will be executed by MySQL when the carriage returns. such as entering the following statement

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

  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< 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 not possible to wait until the user has entered all of these statements before executing the entire statement. Since MySQL encounters a semicolon, it will be executed automatically. That is, the MySQL database interpreter will execute when the statement return '; In this case, the delimiter must be replaced with other symbols such as//or $$ in advance.

  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< 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;//

This allows the MySQL interpreter to 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 for the user agent of the statistics website. This is the SQL code below.

  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 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: "//"

  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, MySQL delimiter symbols can be set freely, you can use "/" or "$$" and so on. But the more common usage in MySQL database storage is "//" and "$$". The above-mentioned code in SQLYOG is not executed by the MySQL command line client.

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 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//--no sign 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 sign "//" at the end

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

    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 directive 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, the client tools for the MySQL database are visible in some places, each with its own set.

Overview of the role of delimiter in MySQL database

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.