【SQL编程】MySQL 5

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

> 
> 录入数据
> 
> 
> 

INSERT INTO test.tb\_test\_json ( id, json\_str )
VALUES
( ‘001’, ‘{ “age”: 10, “gender”: “male”, “hobby”: [ { “describe”: “健身时听摇滚乐，身心愉悦”, “type”: “music” }, { “describe”: “偶尔垂钓，纯属娱乐”, “type”: “fishing” } ], “name”: “杰森”}’ );
INSERT INTO test.tb\_test\_json ( id, json\_str )
VALUES
( ‘002’, ‘{ “age”: 20, “gender”: “female”, “hobby”: [ { “describe”: “买买买，钱包被掏空”, “type”: “shopping” }, { “describe”: “东南西北，美食在心中”, “type”: “food” } ], “name”: “贝比”}’ );
INSERT INTO test.tb\_test\_json ( id, json\_str )
VALUES
( ‘003’, ‘{ “age”: 30, “gender”: “female”, “name”: “汤姆”}’ );

**测试说明** JSON相关的函数，大部分我们看名字就知道函数的作用：


![在这里插入图片描述](https://img-blog.csdnimg.cn/19870dd5b7bc4e4fa9431fe090e8e6f9.png#pic_center)


#### 2.1 判读是否是合法的JSON类型

SELECT json_valid( ‘hello’ ), json_valid( ‘“hello”’ );

![在这里插入图片描述](https://img-blog.csdnimg.cn/140220d82d7942099a29cd456c317235.png#pic_center)


#### 2.2 JSON文本深度

SELECT id, json_depth( json_str ) FROM tb_test_json;

![在这里插入图片描述](https://img-blog.csdnimg.cn/7355ccd7bcc14fdbb36d3d8884f87f87.png#pic_center)


#### 2.3 JSON文本长度

SELECT id, json_length( json_str ) FROM tb_test_json;

![在这里插入图片描述](https://img-blog.csdnimg.cn/86b9c53fcc424fe484ef22acbbe81f66.png#pic_center)


#### 2.4 JSON值类型

SELECT id, json_type( json_str ) FROM tb_test_json;

![在这里插入图片描述](https://img-blog.csdnimg.cn/c4a9d00d47f14b87b064b8674b045b5d.png#pic_center)


#### 2.5 JSON的keys

SELECT id, json_keys( json_str ) FROM tb_test_json;

![在这里插入图片描述](https://img-blog.csdnimg.cn/491701f7cd8347ddbe4f47c0627d1b35.png#pic_center)


#### 2.6 JSON值获取

SELECT
id,
json_extract( json_str, ‘$.nam{e}^{\prime })ASname0,jso{n}_{e}xtract(jso{n}_{s}tr{,}^{\prime }$.age’ ) AS age0,
json_str ->> ‘$.nam{e}^{\prime }ASname1,jso{n}_{s}tr->{>}^{\prime }$.age’ AS age1,
json_str -> ‘$.nam{e}^{\prime }ASname2,jso{n}_{s}tr-{>}^{\prime }$.age’ AS age2
FROM
tb_test_json;

#### 2.7 JSON数据解析



> 
> 单箭头获取值 双箭头获取字符串
> 
> 
> 

– 【可以】获取结果
SELECT * FROM tb_test_json WHERE json_str -> ‘KaTeX parse error: Undefined control sequence: \* at position 32: …可以】获取结果 SELECT \̲*̲ FROM tb_test_j….age’ = ‘20’;
– 【不可以】获取结果
SELECT * FROM tb_test_json WHERE json_str -> ‘$.age’ = ‘20’;

> 
> 数据筛选加解析【不同深度】
> 
> 
> 

SELECT
id,
json_extract( json_str, ‘$.nam{e}^{\prime })ASname0,jso{n}_{e}xtract(jso{n}_{s}tr{,}^{\prime }$.age’ ) AS age0,
json_extract( json_str, ‘$.hobby[0].typ{e}^{\prime })AShobby0,jso{n}_{s}tr->{>}^{\prime }$.name’ AS name1,
json_str ->> ‘$.ag{e}^{\prime }ASage1,jso{n}_{s}tr->{>}^{\prime }$.hobby[0].type’ AS hobby1,
json_str -> ‘$.nam{e}^{\prime }ASname2,jso{n}_{s}tr-{>}^{\prime }$.age’ AS age2,
json_str -> ‘$.hobby[0].typ{e}^{\prime }AShobby2FROMt{b}_{t}es{t}_{j}sonWHEREjso{n}_{s}tr->{>}^{\prime }$.name’ = ‘杰森’ OR json_str -> ‘$.ag{e}^{\prime }=30ORjso{n}_{s}tr-{>}^{\prime }$.hobby[0].type’ = ‘shopping’;

![在这里插入图片描述](https://img-blog.csdnimg.cn/f7f3453af0334a6e920a00df2f98c8be.png#pic_center)