mysql根据子节点id查出所有父节点

1.表结构

CREATE TABLE `tree` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) DEFAULT NULL, `tname` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4;

2.sql语句

SELECT @r AS _id, (SELECT @r := pid FROM tree WHERE id = _id) AS _pid FROM (SELECT @r := 8) vars, tree h WHERE @r <> 0

3.结果