mysql 递归查询上级\下级

1. 建表

CREATE TABLE `axc_users`  (
  `u_id` int(11) NULL DEFAULT NULL,
  `u_parent_id` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of axc_users
-- ----------------------------
INSERT INTO `axc_users` VALUES (1, 1, '用户1');
INSERT INTO `axc_users` VALUES (2, 1, '用户2');
INSERT INTO `axc_users` VALUES (3, 2, '用户3');
INSERT INTO `axc_users` VALUES (4, 2, '用户4');

2. 查询下级信息

SELECT
	t3.u_id, t3.u_parent_id, t3.name, t3.ischild
FROM
	(
SELECT
	t1.*,
IF
	( find_in_set( u_parent_id, @pids ) > 0, @pids := concat( @pids, ',', u_id ), 0 ) AS ischild 
FROM
	( SELECT * FROM axc_users t ORDER BY u_parent_id, u_id ) t1,
	( SELECT @pids := 2[查询用户ID] ) t2 
	) t3 
WHERE
	ischild != 0

# axc_users [表名]
# u_id [用户ID]
# u_parent_id [上级ID]

3. 查询上级信息

SELECT
	T2.u_id, T2.u_parent_id, T2.name
FROM
	(
SELECT
	@r AS _id,
	( SELECT @r := u_parent_id FROM axc_users WHERE u_id = _id ) AS u_parent_id,
	@l := @l + 1 AS lvl 
FROM
	( SELECT @r := 4[查询用户ID], @l := 0 ) vars,
	axc_users h 
WHERE
	@r <> 0 
	) T1
	JOIN axc_users T2 ON T1._id = T2.u_id 
ORDER BY
	u_id
LIMIT 2[查询深度]

# axc_users [表名]
# u_id [用户ID]
# u_parent_id [上级ID]