mysql-递归查询（二）

情景描述

mysql 表查询，经常有通过子不断查询父，直至查询不到为止。

数据准备

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `city`
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
  `id` bigint(20) NOT NULL,
  `name` varchar(40) NOT NULL,
  `parentId` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `city`
-- ----------------------------
BEGIN;
INSERT INTO `city` VALUES ('1', '中国', '0'), ('2', '北京', '1'), ('3', '山西', '1'), ('4', '海淀', '2'), ('5', '昌平', '2'), ('6', '太原', '3'), ('7', '晋城', '3'), ('8', '小店', '6');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

递归sql

@r := #{id}

SELECT
	t1.*
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := parentId
				FROM
					city
				WHERE
					id = _id
			) AS parentId,
			@l := @l + 1 AS lvl
		FROM
			(SELECT @r := 8, @l := 0) vars,
			city h
	) t2
JOIN city t1 ON t1.id = t2._id