虽然MySQL5.7开始原生支持json格式数据,但是如果需要把数据库表中的树结构json解析转成相应树表结构的话,一般是通过c#、java、js等语言解析完成再写入table的。其实,在MySQL内部,不需借助其它语言,就能完成转换。
- 多层嵌套json数据示例
以下json字符串存储在treejson表的tree字段中
{ "children" : [ { "id" : 2, "name" : "西安", "children" : [] } ], "id" : 1, "name" : "陕西" }
- 表treejson结构
列名---数据类型---描述
num---INT(11)-----自增id
id-------INT(11)-----treeId
tree----JSON-------treeJson
- 使用表treetable接收转换后的数据
列名---数据类型---描述
num-----INT(11)------------自增id
rootId---INT(11)------------tree根节点id
id--------INT(11)-------------treeNodeId
pId------INT(11)-------------treePNodeId
name---VARCHAR(45)---nodeName
path-----VARCHAR(50)---nodePath
level-----TINYINT(2)-------nodeLevel
- 使用存储过程转换
CREATE DEFINER=`root`@`localhost` PROCEDURE `extractjson`(IN jsonPath varchar(100), IN treePath varchar(50), IN parentId int, IN treeLevel int, IN treeJsonId int) BEGIN # 当前节点id DECLARE tempId INT DEFAULT '0'; # 当前节点下属的子节点数目 DECLARE childrenLen INT DEFAULT '0'; # 当前节点下属的子节点json的解析路径 DECLARE nextJsonPath varchar(100) DEFAULT ''; # 设置变量值 SET tempId = (SELECT JSON_EXTRACT(tree,CONCAT(jsonPath,'.id')) FROM treejson WHERE id = treeJsonId); SET childrenLen = (SELECT JSON_LENGTH(tree,CONCAT(jsonPath,'.children')) FROM treejson WHERE id = treeJsonId); # 内置参数必要的递归层深限制 SET @@max_sp_recursion_depth = 100; # 把当前节点插入到数据表 INSERT INTO treetable(rootId,id,pId,name,path,level) SELECT JSON_EXTRACT(tree,'$.id') AS rootId ,tempId AS id ,parentId AS pId ,JSON_EXTRACT(tree,CONCAT(jsonPath,'.name')) AS name ,CONCAT(treePath,',',tempId) AS path ,(treeLevel + 1) AS level FROM treejson WHERE treejson.id = treeJsonId; # 循环把所有子节点插入数据表 WHILE childrenLen > 0 DO SET nextJsonPath = CONCAT(jsonPath, '.children[', childrenLen - 1, ']'); CALL extractjson(nextJsonPath, CONCAT(treePath,',',tempId), tempId, (treeLevel + 1), treeJsonId); SET childrenLen = childrenLen - 1; END WHILE; END