<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. 表结构

同向上递归查询id表结构

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);

• 结果