After MYSQL exports a SQL:
DELIMITER $$
DROP TRIGGER IF EXISTS `updateegopriceondelete`$$
CREATE
TRIGGER `updateegopriceondelete` AFTER DELETE ON `customerinfo`
FOR EACH ROW BEGIN
DELETE FROM egoprice WHERE customerId=;
END$$
DELIMITER ;
The DELIMITER is determined to end the character "$$", and then finally defined as ";", and the default end character of MYSQL is ";".
Detailed explanation:
In fact, it is to tell the mysql interpreter whether the command has ended and whether mysql can be executed.
By default, the delimiter is a semicolon;. In the command line client, if there is a line of command ending with a semicolon,
Then after pressing, mysql will execute the command. If you enter the following statement
mysql> select * from test_table;
Then press Enter, then MySQL will execute the statement immediately.
But sometimes, I don't want MySQL to do so. There are more statements that may be entered, and 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 to wait until the user has entered all these statements before executing the entire paragraph of statements.
Because as soon as mysql encounters a semicolon, it must be executed automatically.
That is, when the statement RETURN '';, the mysql interpreter is about to be executed.
In this case, delimiter needs to be replaced with other symbols, 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 after // 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 is run under MySQL 5.0.41-community-nt.
A MySQL stored procedure is written to count the user agent. This 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 under the MySQL graphics client EMS SQL Manager 2005 for MySQL. However, an error occurs in the SQLyog MySQL GUI v5.02 client. Finally, the reason was found is that the delimiter was not set up properly. By default, delimiter ";" is used to submit query statements to MySQL. In the stored procedure, there is a ";" at the end of each SQL statement. If ";" is submitted to MySQL at this time, of course there will be a problem. So I changed the MySQL delimiter, and the above MySQL stored procedure was programmed like this:
Delimiter //; -- Change the 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
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 the default MySQL delimiter: ";"
Of course, the MySQL delimiter symbol can be set freely, you can use "/" or "$$" etc. However, the most common usages in MySQL stored procedures are "//" and "$$". The above code in SQLyog was moved to the MySQL Command Line Client but could not be executed.
mysql> delimiter //; -- Change the 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 the default MySQL delimiter: ";"
-> //
-> //
-> //
-> ;
-> ;
->
It's really strange! Finally, I found the problem. Run "delimiter //; " under the MySQL command line, and then the delimiter of MySQL is actually "//;", rather than the "//" we expected. In fact, just run the command "delimiter //" and it will be OK.
mysql> delimiter // -- Don't sign at the end ";"
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 ; -- Don't sign at the end "//"
mysql>
By the way, we can execute SQL code in a file in a MySQL database. For example, I put the code of the stored procedure above in the file d:\pr_stat_agent.sql. You can run the following code to create a stored procedure.
mysql> source d:\pr_stat_agent.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
The abbreviation of the source directive is: "\."
mysql> \. d:\pr_stat_agent.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Finally, it can be seen that MySQL's client tools are in their own right and have their own set in some places.
DELIMITER $$
DROP TRIGGER IF EXISTS `updateegopriceondelete`$$
CREATE
TRIGGER `updateegopriceondelete` AFTER DELETE ON `customerinfo`
FOR EACH ROW BEGIN
DELETE FROM egoprice WHERE customerId=;
END$$
DELIMITER ;
The DELIMITER is determined to end the character "$$", and then finally defined as ";", and the default end character of MYSQL is ";".
Detailed explanation:
In fact, it is to tell the mysql interpreter whether the command has ended and whether mysql can be executed.
By default, the delimiter is a semicolon;. In the command line client, if there is a line of command ending with a semicolon,
Then after pressing, mysql will execute the command. If you enter the following statement
mysql> select * from test_table;
Then press Enter, then MySQL will execute the statement immediately.
But sometimes, I don't want MySQL to do so. There are more statements that may be entered, and 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 to wait until the user has entered all these statements before executing the entire paragraph of statements.
Because as soon as mysql encounters a semicolon, it must be executed automatically.
That is, when the statement RETURN '';, the mysql interpreter is about to be executed.
In this case, delimiter needs to be replaced with other symbols, 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 after // 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 is run under MySQL 5.0.41-community-nt.
A MySQL stored procedure is written to count the user agent. This 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 under the MySQL graphics client EMS SQL Manager 2005 for MySQL. However, an error occurs in the SQLyog MySQL GUI v5.02 client. Finally, the reason was found is that the delimiter was not set up properly. By default, delimiter ";" is used to submit query statements to MySQL. In the stored procedure, there is a ";" at the end of each SQL statement. If ";" is submitted to MySQL at this time, of course there will be a problem. So I changed the MySQL delimiter, and the above MySQL stored procedure was programmed like this:
Delimiter //; -- Change the 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
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 the default MySQL delimiter: ";"
Of course, the MySQL delimiter symbol can be set freely, you can use "/" or "$$" etc. However, the most common usages in MySQL stored procedures are "//" and "$$". The above code in SQLyog was moved to the MySQL Command Line Client but could not be executed.
mysql> delimiter //; -- Change the 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 the default MySQL delimiter: ";"
-> //
-> //
-> //
-> ;
-> ;
->
It's really strange! Finally, I found the problem. Run "delimiter //; " under the MySQL command line, and then the delimiter of MySQL is actually "//;", rather than the "//" we expected. In fact, just run the command "delimiter //" and it will be OK.
mysql> delimiter // -- Don't sign at the end ";"
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 ; -- Don't sign at the end "//"
mysql>
By the way, we can execute SQL code in a file in a MySQL database. For example, I put the code of the stored procedure above in the file d:\pr_stat_agent.sql. You can run the following code to create a stored procedure.
mysql> source d:\pr_stat_agent.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
The abbreviation of the source directive is: "\."
mysql> \. d:\pr_stat_agent.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Finally, it can be seen that MySQL's client tools are in their own right and have their own set in some places.