Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the role of delimiter in MySQL". In daily operation, I believe many people have doubts about the role of delimiter in MySQL. Xiaobian consulted all kinds of information and sorted out simple and easy operation methods. I hope to help you answer the doubts of "what is the role of delimiter in MySQL"! Next, please follow the small series to learn together!

What is the function of delimiter in MySql?

This command has nothing to do with stored procedures.

In fact, it tells the mysql interpreter whether the command has ended and whether mysql can be executed.

By default, delimiter is a semicolon;. On the command line client, if a command line ends with a semicolon,

Then after carriage return, mysql will execute the command. For example, enter the following statement

mysql> select * from test_table;

Then enter, MySQL will execute the statement immediately.

But sometimes you don't want MySQL to do that. There may be more statements entered in, and the statement contains a semicolon.

For example, 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 RETURN LEFT(S, N);

mysql> ELSE

mysql> IF CHAR_LENGTH(S) 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 to wait until the user has typed all of these statements before executing the entire paragraph.

As soon as mysql encounters a semicolon, it executes automatically.

That is, when the statement RETURN '';, the mysql interpreter is about to execute.

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 RETURN LEFT(S, N);

mysql> ELSE

mysql> IF CHAR_LENGTH(S) RETURN S;

mysql> ELSE

mysql> RETURN CONCAT(LEFT(S, N-10), '... ', RIGHT(S, 5));

mysql> END IF;

mysql> END IF;

mysql> END;//

This way, the mysql interpreter executes the statement only when//appears

Examples:

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 under MySQL 5.0.41-community-nt.

Write a MySQL stored procedure that counts website visits (user agent). 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; 我在 EMS SQL Manager 2005 for MySQL 这个 MySQL 图形客户端下可以顺利运行。但是在 SQLyog MySQL GUI v5.02 这个客户端就会出错。最后找到原因是没有设置好 delimiter 的问题。默认情况下，delimiter ";" 用于向 MySQL 提交查询语句。在存储过程中每个 SQL 语句的结尾都有个 ";"，如果这时候，每逢 ";" 就向 MySQL 提交的话，当然会出问题了。于是更改 MySQL 的 delimiter，上面 MySQL 存储过程就编程这样子了： delimiter //; -- 改变 MySQL delimiter 为："//"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 ; // -- 改回默认的 MySQL delimiter：";" 当然，MySQL delimiter 符号是可以自由设定的，你可以用 "/" 或者"$$" 等。但是 MySQL 存储过程中比较常见的用法是 "//" 和 "$$"。上面的这段在 SQLyog 中的代码搬到 MySQL 命令客户端（MySQL Command Line Client）却不能执行。 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 default MySQL delimiter: ";" -> // -> // -> // -> ; -> ; ->

It was really strange! Finally, the problem was discovered. Running "delimiter //; " on the MySQL command line meant that MySQL's delimiter was actually "//;" instead of "//" as we expected. In fact, just run the command "elimiter//" and it will be OK.

mysql> delimiter // --last trailing no 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 ; --Do not sign "//"mysql> at the end

Incidentally, we can execute SQL code in files in MySQL databases. For example, I put the code for the stored procedure above in the file d: \pr_stat_agent.sql. You can create a stored procedure by running the following code.

mysql> source d:\pr_stat_agent.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)

The abbreviation for 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)

Finally, you can see that MySQL's client tools are fragmented in some places.

At this point, the study of "what is the role of delimiter in MySQL" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.