<a name="向上递归查询id"></a>
一、向上递归查询id
1.说明
目录树表结构生成拼接的Id(1#2#3)
2. 表结构
<a name="向上递归查询id表结构"></a>
CREATE TABLE `t1` (
`id` int NOT NULL ,
`p_id` int DEFAULT NULL COMMENT '父级id',
`name` varchar(255) DEFAULT NULL,
`path` varchar(255) DEFAULT NULL COMMENT '目录id树',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t1` VALUES (1, NULL, 'a', NULL);
INSERT INTO `t1` VALUES (2, 1, 'b', NULL);
3.递归查询函数
CREATE FUNCTION f_t1(id INT)
RETURNS VARCHAR(255) READS SQL DATA
BEGIN
/*
向上递归查询id 返回 pid#id 格式
*/
DECLARE t_id INT;
DECLARE c INT; -- 当前id是否存在
DECLARE path VARCHAR(255);
SET t_id=id;
SET path='';
SELECT count(1) INTO c FROM t1 WHERE t1.id=t_id;
IF c=0 THEN
RETURN NULL; -- 如果当前值不存在 返回 NULL
END IF;
WHILE t_id IS NOT NULL DO -- 无父级id
SET path=CONCAT(t_id,'#',path);
SELECT t1.p_id INTO t_id FROM t1 WHERE t1.id=t_id; -- 查询父级id
END WHILE;
RETURN SUBSTR(path,1,LENGTH(path)-1);
END
4.查询演示
a) 正常值1
- 查询
SELECT f_t1(1);
-
结果
SELECT f_t1(1)
b) 正常值2
- 查询
SELECT f_t1(2);
-
结果
SELECT f_t1(2)
b) 无效值3
- 查询
SELECT f_t1(3);
-
结果
SELECT f_t1(3)
二、触发器生成id树
1.说明
根据向上递归查询id功能自动生成path
*注意上面的f_t1方法不适用于这个，这里重新构建了一种，因为触发器在BEFORE时数据还未插入，所以会导致上面方法查询始终为null
*注意id自动递增的表不适用此方法,因为BEFORE时id还未生成，如使用AFTER数据又无法修改
2. 表结构
3.sql
-- 向上递归查询id
CREATE FUNCTION f_t1(id INT,p_id INT)
RETURNS VARCHAR(255) READS SQL DATA
BEGIN
/*
向上递归查询id 返回 pid#id 格式
id 当前id
p_id 父级id
*/
DECLARE t_id INT;
DECLARE t_p_id INT;
DECLARE path VARCHAR(255);
SET path=CAST(id AS CHAR);
IF p_id IS NULL THEN -- 父级id为空时返回当前id值
RETURN path;
END IF;
SET t_p_id=p_id;
a: LOOP
SET t_id=NULL; -- 初始化当前id为空,否则无数据时会保留上次记录
SELECT t.id INTO t_id FROM t1 t WHERE t.id=t_p_id;
IF t_id IS NOT NULL THEN -- 如果通过父级id查询的结果存在，就查询其父级id然后继续循环
SELECT t.p_id INTO t_p_id FROM t1 t WHERE t.id=t_p_id;
SET path=CONCAT(t_id,'#',path);
ELSE -- 如果通过父级id查询的结果不存在,就结束循环
LEAVE a;
END IF;
END LOOP a;
RETURN path;
END;
-- 创建触发器
CREATE TRIGGER tg_t1 BEFORE INSERT ON t1 FOR EACH ROW SET new.path=f_t1(new.id,new.p_id); -- 在插入数据前，修改数据
/*
说明：
BEFORE INSERT 在数据插入前操作
new 插入的数据
*/
4.查询演示
a) 插入记录
- 插入
INSERT INTO `t1` VALUES (3, 2, 'c', NULL);
-
结果
