I use the mysql custom function, which always returns 0. I don"t know how to do it. `CREATE FUNCTION CountLayer (typeid int) RETURNS int (12)
BEGIN
-sharpRoutine body goes here...
declare result int;
declare lft int;
declare rgt int;
declare c int;
set c = 0;
set result = 0;
set lft = 0;
set rgt = 0;
select count(*) into result from tree where Lft <= 2 and Rgt >= 11;
RETURN result;
END`
Thetable structure data is like this.
DROP TABLE IF EXISTS -- always returns 0, but the actual select count (*) from tree where Lft < = 2 and Rgt > = 11; the query result is 2. what"s going on? tree
;
CREATE TABLE tree
(
Type_id
int (12) UNSIGNED NOT NULL AUTO_INCREMENT,
Name
varchar (20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Lft
int (12) NOT NULL,
Rgt
-- Records of tree
-
INSERT INTO tree
VALUES (1, "commodity", 1, 18);
INSERT INTO tree
VALUES (2, "food", 2, 11);
INSERT INTO tree
VALUES (3, "meat", 3,6);
INSERT INTO tree
VALUES (4, "pork", 4,5);
INSERT INTO tree
VALUES (5, "vegetables", 7,10);
INSERT INTO tree
VALUES (6, "cabbage", 8,9);
INSERT INTO tree
VALUES (7, "Electrical", 12,17);
INSERT INTO tree
VALUES (8,"TV")
if I set result = 100, then return is OK. That"s weird.
what does Baidu say? to turn on the custom function switch, mine is on.