mysql - 抱歉，发生了意外错误! SQL错误

Question

我是 MYSQL 新手，在执行给定过程时遇到问题。它在 SQL 版本 5.7.28 上运行完美，但在 SQL 5.6.41 上抛出以下错误

ERROR:
SQL query:


create procedure menus_hier_upward
(
in p_menu_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0)
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

我正在执行的程序是:

create procedure menus_hier_upward
(
in p_menu_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

create temporary table hier(
 parent_id smallint unsigned, 
 menu_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select menu_id, null, v_dpth from menus where menu_id = p_menu_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from menus m inner join hier on m.menu_id = hier.parent_id and hier.depth = v_dpth) then

        insert into hier select m.parent_id, m.menu_id, v_dpth + 1 
            from menus m inner join tmp on m.menu_id = tmp.parent_id and tmp.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table tmp;
        insert into tmp select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select 
-- m.menu_id,
 m.name as menu_name
-- p.menu_id as parent_id,
-- p.name as parent_menu_name,
-- hier.depth
from 
 hier
inner join menus m on hier.menu_id = m.menu_id
left outer join menus p on hier.parent_id = p.menu_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

我使用的服务器配置是:

• PHP 7.3
• MYSQL 5.6.41

如果有人能告诉我我做错了什么，我将不胜感激。谢谢

Answer 1

您遇到的问题似乎与程序分隔符的定义有关:

https://dev.mysql.com/doc/refman/5.6/en/stored-programs-defining.html

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command. The following example shows how to do this for the dorepeat() procedure just shown. The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

这意味着您的代码应该是:

delimiter //

create procedure menus_hier_upward
  (
    in p_menu_id smallint unsigned
  )
begin

declare v_done tinyint unsigned default 0;
declare v_dpth smallint unsigned default 0;

create temporary table hier(
 parent_id smallint unsigned, 
 menu_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select menu_id, null, v_dpth from menus where menu_id = p_menu_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from menus m inner join hier on m.menu_id = hier.parent_id and hier.depth = v_dpth) then

        insert into hier select m.parent_id, m.menu_id, v_dpth + 1 
            from menus m inner join tmp on m.menu_id = tmp.parent_id and tmp.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table tmp;
        insert into tmp select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select 
    -- m.menu_id,
    m.name as menu_name
    -- p.menu_id as parent_id,
    -- p.name as parent_menu_name,
    -- hier.depth
from 
  hier
inner join menus m on hier.menu_id = m.menu_id
left outer join menus p on hier.parent_id = p.menu_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end //

delimiter ;

您可以在这里测试:http://sqlfiddle.com/#!9/ed86e6

请注意构建架构按钮右侧的“//”分隔符设置