MySql高效实现递归

MySql从8.0开始支持递归查询了

CREATE TABLE `t_area` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

insert  into `t_area`(`id`,`parent_id`,`name`) values 
(1,0,'福建省'),
(2,1,'泉州市'),
(3,1,'福州市'),
(4,1,'厦门市'),
(5,2,'丰泽区'),
(6,2,'鲤城区'),
(7,4,'思明区'),
(8,4,'湖里区'),
(9,4,'翔安区'),
(10,7,'莲前街道');

WITH recursive temp AS
(SELECT
  *
FROM
  t_area
WHERE NAME = '厦门市'
UNION
ALL
SELECT
  a.*
FROM
  t_area a
  JOIN temp
    ON a.parent_id = temp.id)
SELECT
  *
FROM
  temp;

这里得益于MySql8.0 和Oracle一样支持 with temp as (临时表)