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=OLD.customerId;
    END$$
DELIMITER;

where DELIMITER set the terminator as "$$", and finally defined as ";", the default terminator of MYSQL is ";".



Detailed explanation:

In fact, it tells the mysql interpreter whether this command has ended, mysql can be executed.
By default, delimiter is a semicolon;.In the command line client, if there is a line of command ending with a semicolon,
then press Enter and mysql will execute the command.If you enter the following statement
mysql> select * from test_table;
and then press Enter, MySQL will execute the statement immediately.

But sometimes, you don't want MySQL to do this.It is possible to enter more sentences, and the sentences contain semicolons.
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, no It may wait until the user has input all these statements before executing the entire statement.
Because mysql will execute automatically as soon as it encounters a semicolon.
That is, in the statement RETURN'';, the mysql interpreter is about to execute.
In this case, you need to replace delimiter with other symbols in advance, 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;//
So only when//appears, mysql The interpreter will execute this statement


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;

mysq l> 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.

I wrote a MySQL stored procedure that counts website visits (user agent).This is 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;
I can run smoothly under the MySQL graphical client EMS SQL Manager 2005 for MySQL.But in SQLyog MySQL GUI v5.02 this client will be wrong.Finally, the reason was found that the delimiter was not set.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.If at this time, every time ";" is submitted to MySQL, of course there will be a problem.So change the MySQL delimiter, the above MySQL stored procedure is 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 "$$" and so on.But the more common usage in MySQL stored procedure is "//" and "$$".The above code in SQLyog is moved to the MySQL Command Line Client but cannot be executed.

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 the default MySQL delimiter: " ;"
   ->//
   ->//
   ->//
   ; ->;
   ->;
   ->

It's weird! Finally, I found the problem.When running "delimiter//;" under the MySQL command line, the MySQL delimiter is actually "//;" instead of the "//" we expected.In fact, just run the command "delimiter//" and it's OK.

mysql> delimiter//    -Do not use the symbol ";" 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 use the symbol "//" at the end
mysql>

By the way, we can use M The SQL code in the file is executed in the ySQL database.For example, I put the code of the above stored procedure 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 abbreviated form of the source command 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 the MySQL client tools are different in some places, and each has its own set.