MYSQL sql整理

小小小小小小小小小小码农

已于 2023-12-22 16:48:54 修改

阅读量173

点赞数

文章标签： mysql sql

于 2023-06-02 13:54:16 首次发布

本文链接：https://blog.csdn.net/sinat_38314794/article/details/131004938

版权

本文介绍了在MySQL中如何进行层级数据的查询，包括从父级查询所有子级和从子级查询所有父级的方法。同时，展示了数据补全的示例，如使用LPAD和RPAD函数。文章还详细探讨了JSON操作，如JSON_TABLE和JSON_EXTRACT用于从JSON数组中提取信息，并将其转换为多行数据。最后提到了虚列的概念，例如通过MD5加密生成虚拟列。

摘要由CSDN通过智能技术生成

1. 层级数据的查询

test

id	name	parent_id
1	第一层级	0
2	第二层级	1

1.1 依据一个父级查询所有子级

SELECT au.id, au.name, au.parent_id
FROM (SELECT id,name,parent_id FROM test WHERE parent_id IS NOT NULL) au,
     (SELECT @pid := 1) pd
WHERE FIND_IN_SET(parent_id, @pid) > 0
          AND @pid := concat(@pid, ',', id)
UNION
SELECT id, name, parent_id
FROM test
WHERE id = 1
ORDER BY id;

1.2 依据一个子级查询所属所有父级

SELECT t2.id, t2.name, t2.parent_id
FROM (SELECT @r as _id,
             (SELECT @r := parent_id FROM test WHERE id = _id) as pid,
             @l := @l + 1 as lvl
      FROM (SELECT @r := 54, @l := 0) vars, test as h
      WHERE @r <> 0) t1
         JOIN test t2
              ON t1._id = t2.id
ORDER BY T1.lvl DESC;

2.数据补全

select LPAD(1,3,0),RPAD(21,10,0)

3.JSON

3.1JSON 数组转成多行

select caja
from t_table,
     JSON_TABLE(column_a_json_arr,"$[*]" columns (caja text path '$')) as t2
;
-- eg 表中t_table json字段 column_a_json_arr 有数据 
-- ["ssdfs","sdfasfds"]
-- ["dsfasdfsda"，"sdfasfds"]
-- []
-- ["dfaefasdfadsfasdfge"]
-- 执行后
-- ssdfs
-- sdfasfds
-- dsfasdfsda
-- sdfasfds
-- dfaefasdfadsfasdfge

-- 将上述结果分组去重后重新组成一个数组
select id , JSON_ARRAYAGG(caja) as cajas
from (
         SELECT
             distinct id,
             caja
         FROM
             t_table,
             JSON_TABLE(column_a_json_arr,"$[*]" columns (caja text path '$')) as t2
         order by caja
     ) t1 group by id
;
-- 输出结果 ["ssdfs","sdfasfds","dsfasdfsda","dfaefasdfadsfasdfge"]

3.2 json 数据查询

JSON_EXTRACT(字段名，Path取数表达式)

-- json 查询
-- json 结构:
{
    "id1": 1,
    "id2": "2",
    "id3": 3
}
-- 
select JSON_EXTRACT('{"id1": 1,"id2":"2","id3": 3}','$.id1');
-- 输出：1 

-- jsonArr查询
-- json 结构: 
[
    {
        "id1": 1,
        "id2": "2",
        "id3": 3
    },
    {
        "id1": 11,
        "id2": "22",
        "id3": 33
    }
]
-- 
select JSON_EXTRACT('[{"id1": 1,"id2":"2","id3": 3},{"id1": 11,"id2":"22","id3": 33}]','$[*].id1');
-- 输出： [1, 11]

-- json 内部嵌套jsonArr 查询 
-- json 结构:
[
    {
        "id1": [
            {
                "id1_1": 1
            },
            {
                "id1_2": 2
            }
        ],
        "id2": "2",
        "id3": 3
    },
    {
        "id1": 11,
        "id2": "22",
        "id3": 33
    }
]
-- 
select JSON_EXTRACT('[{"id1": [{"id1_1":1},{"id1_2":2}],"id2":"2","id3": 3},{"id1": 11,"id2":"22","id3": 33}]','$[*].id1[*].id1_1');
--输出： [1]

JSON_TABLE 与 JSON_EXTRACT 结合可以将 获取的json结果转成多行


-- json 结构：
[
    {
        "id1": [
            {
                "id1_1": 1
            },
            {
                "id1_1": 2
            }
        ],
        "id2": "2",
        "id3": 3
    },
    {
        "id1": 11,
        "id2": "22",
        "id3": 33
    }
]
-- 获取 id1_1 对应的值，多行返回

select t1.js,t2.id from (
                  select '[{"id1": [{"id1_1":1},{"id1_1":2}],"id2":"2","id3": 3},{"id1": 11,"id2":"22","id3": 33}]' as js
              )t1 ,
              JSON_TABLE(JSON_EXTRACT(js, '$[*].id1[*].id1_1'), '$[*]'
                         COLUMNS (id VARCHAR(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci PATH '$' )) AS t2

结果输出：

使用表达式查询json

select js-> '$.id1' as id1 from (
select '{"id1": 1,"id2":"2","id3": 3}' as js
    )t ;
-- 输出： 1

4.虚列

-- eg: 增加一个虚列字段 column_xxx 由字段x1,x2 md5加密得到
alter table t_xxx_table add column column_xxx data_type generated always as (md5(concat(x1,x2))) VIRTUAL ;