mysql中json类型的使用

冷不防 2022-10-20 12:13 153阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70][]

![20210517221632553.png][]![20210517221648309.png][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 2][]

-- 创建json数组
    SELECT JSON_ARRAY(20,30,50)
    -- 查询json数组是否包含某个元素,mysql中0和1代表boolean类型.第二个参数必须是字符串
    SELECT JSON_CONTAINS(JSON_ARRAY(20,30,50), "100")

![20210517215304409.png][]

-- 查询用户拥有的角色
    SELECT r.role_name FROM tb_user u
    LEFT JOIN tb_role r ON JSON_CONTAINS(u.role, CAST(r.id AS CHAR))
    WHERE U.id = 3

![20210517220137316.png][]

-- 查询用户的权限(即用户的角色对应的权限)
    SELECT DISTINCT p.permission_name FROM tb_user u
    LEFT JOIN tb_role r ON JSON_CONTAINS(u.role, CAST(r.id AS CHAR))
    LEFT JOIN tb_permission p ON JSON_CONTAINS(r.permissions, CAST(p.id AS CHAR))
    WHERE U.id = 3

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 3][]

有以下json数据

[{"sn": "", "date": ["2020-11-4", "2020-11-5"], "endDate": "2020-11-5", "external": [{"values": ["F53S3C2", "F53S3D"], "fieldName": "standard_model", "displayName": "阿里机型"}], "isDragon": "", "rangeType": "2", "reportNum": 345, "startDate": "2020-11-4"},{"sn": "", "date": ["2020-11-4", "2020-11-5"], "endDate": "2020-11-5", "external": [{"values": ["A53S3C2", "A53S3D"], "fieldName": "standard_model", "displayName": "阿里机型"}], "isDragon": "", "rangeType": "2", "reportNum": 555, "startDate": "2020-11-4"}]

![20210609223910565.png][]

SELECT
     *
    FROM
     USER t 
    WHERE JSON_SEARCH(
        # 确定搜索范围
        JSON_EXTRACT( t.range_dimension, '$[*].external[*].values[*]' ),
        # 匹配多行
        'all',
        # 模糊匹配关键字
        'F53%'
       ) IS NOT NULL 
    ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70]: /images/20221020/ccf5ab44b1334cf99b61fcb31d57b436.png
[20210517221632553.png]: https://img-blog.csdnimg.cn/20210517221632553.png
[20210517221648309.png]: https://img-blog.csdnimg.cn/20210517221648309.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1]: /images/20221020/40cf5b342fa04e388de7a2ac39973dbf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 2]: /images/20221020/037232ad5ebf41d8907f1648f97e2426.png
[20210517215304409.png]: /images/20221020/c36deaec1dea483c8ed70ae688c1f960.png
[20210517220137316.png]: /images/20221020/cb6c5db92de6402f90b28058c51a5843.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 3]: /images/20221020/019f2150fbe543aa88793b62c4bf2701.png
[20210609223910565.png]: /images/20221020/65bf8393209e4832a25164fbf1c3f373.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 4]: /images/20221020/2463aac2eb144a05a4c9a9a881196f75.png