MySQL 过程语言

Question

SQL 查询:文档

CREATE PROCEDURE tree_add_root()
BEGIN
START TRANSACTION;
$max = SELECT MAX(`rht`) FROM trees;
INSERT INTO trees(`tree_id`, `name`, `label`, `description`, `lft`, `rht`, `lvl`) VALUES(1, 'Index', 'Index', '', 1,$max+1 , 0);
COMMENT;
END;

MySQL 说:文档

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 '= SELECT MAX(rht) FROM trees; INSERT INTO trees(tree_id, name, label, `' at line 4

我该如何解决这个问题？

Answer 1

Use This

CREATE PROCEDURE tree_add_root()
BEGIN
DECLARE v_max int(11);
START TRANSACTION;
SELECT MAX(`rht`) into v_max FROM trees;
INSERT INTO trees(`tree_id`, `name`, `label`, `description`, `lft`, `rht`, `lvl`) VALUES(1, 'Index', 'Index', '', 1,v_max+1 , 0);
COMMIT;
END;

在 MySQL 过程中使用任何变量之前，需要像上面的查询一样首先使用 DECLARE 关键字声明，然后启动主体 (BEGIN)。这里使用 v_max 而不是 $max。

还使用 COMMIT 关键字代替注释。