mysql树形菜单建表及查询

Question

mysql如何建表，如何写查询语句，能得到如下二级递归树形菜单？

[{
    "id": 1,
    "title": "工作空间",
    "icon": "layui-icon layui-icon-console",
    "type": 0,
    "href": "",
    "children": [{
        "id": 10,
        "title": "控制后台",
        "icon": "layui-icon layui-icon-console",
        "type": 1,
        "openType": "_iframe",
        "href": "view/console/console1.html"
    }, {
        "id": 13,
        "title": "数据分析",
        "icon": "layui-icon layui-icon-console",
        "type": 1,
        "openType": "_iframe",
        "href": "view/console/console2.html"
    }, {
        "id": 16,
        "title": "酸爽翻倍",
        "icon": "layui-icon layui-icon-console",
        "type": 1,
        "openType": "_iframe",
        "href": "view/document/core.html"
    }]
},
{
    "id": "component",
    "title": "常用组件",
    "icon": "layui-icon layui-icon-component",
    "type": 0,
    "href": "",
    "children": [{
        "id": 201,
        "title": "基础组件",
        "icon": "layui-icon layui-icon-console",
        "type": 0,
        "children": [
        {
            "id": 2011,
            "title": "功能按钮",
            "icon": "layui-icon layui-icon-face-smile",
            "type": 1,
            "openType": "_iframe",
            "href": "view/document/button.html"
        }, {
            "id": 2013,
            "title": "动态标签",
            "icon": "layui-icon layui-icon-face-cry",
            "type": 1,
            "openType": "_iframe",
            "href": "view/document/tag.html"
        }]
    }, {
        "id": 203,
        "title": "进阶组件",
        "icon": "layui-icon layui-icon-console",
        "type": 0,
        "children": [{
            "id": 2031,
            "title": "数据表格",
            "icon": "layui-icon layui-icon-face-cry",
            "type": 1,
            "openType": "_iframe",
            "href": "view/document/table.html"
        },{
            "id": 2036,
            "title": "卡片组件",
            "icon": "layui-icon layui-icon-face-cry",
            "type": 1,
            "openType": "_iframe",
            "href": "view/document/card.html"
        }]
    }]
}
]

Answer 1

通过sql肯定不现实，把查询到的内容通过递归来进行组织数据。