Recursive CTE in MySql for tree structure (adjacency list)

I'm just starting out with MySQL (I come from using SQL Server previously). I haven't yet started implementing anything in MySQL, just researching how to do things and what problems I might encounter.

In SQL Server I've used CTEs to successfully recurse through an adjacency list table structure to produce the desired result set. From what I can tell so far with MySQL, it does not support CTEs. I've got a fairly simple table structure to hold my hierarchy (written in SQL Server syntax b/c of my familiarity with it):

CREATE TABLE TreeNodes (
    NodeId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ParentNodeId int NULL,
    Name varchar(50) NOT NULL,
    FullPathName varchar(MAX) NOT NULL,  -- '/' delimited names from root to current node
    IsLeaf bit NOT NULL  -- is this node a leaf?
)

Side Note: I realize that FullPathName and IsLeaf are not required and could be determined at query time, but the insert of a tree node will be a very uncommon occurrence as opposed to the queries against this table - which is why I plan to compute those two values as part of the insert开发者_运维技巧 SP (will make the queries that need those two values less costly).

With CTE (in SQL Server), I would have a function like the following to find leaf nodes of current node:

CREATE FUNCTION fn_GetLeafNodesBelowNode (
    @TreeNodeId int
)
RETURNS TABLE
AS
RETURN
WITH Tree (NodeId, Name, FullPathName, IsLeaf)
AS (
    SELECT NodeId, Name, FullPathName, IsLeaf FROM TreeNodes WHERE NodeId = @TreeNodeId
        UNION ALL
    SELECT c.NodeId, c.Name, c.FullPathName, c.IsLeaf FROM Tree t
        INNER JOIN TreeNodes c ON t.NodeId = c.ParentNodeId
)
SELECT * FROM Tree WHERE IsLeaf = 1

How would I do the same with MySQL?

Thanks in advance.

You can get it done by some sort of stored functions and bit logic.

Here is one example.

Have a try.