mysql(一) json数据类型
2021/6/8 19:36:09
本文主要是介绍mysql(一) json数据类型,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
一、MYSQL中json
类型的数据处理
MYSQL 5.7.8中引入了json字段类型
json字段的操作方法
初始化数据
-- 创建表 含有字段id、aley, 其中aley为json类型 mysql> show create table test; +-------+----------------------------------+ | Table | Create Table +-------+----------------------------------+ | test | CREATE TABLE `test` ( `aley` json DEFAULT NULL, `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 | +-------+---------------------------------+ 1 row in set (0.00 sec) -- 插入数据 mysql> insert into test values ('{"name":"aley","age":18}', 1),('{"name":"szx","age":30}',2),('{"name":"wwj","age":35}', 3); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from test; +-----------------------------+----+ | aley | id | +-----------------------------+----+ | {"age": 18, "name": "aley"} | 1 | | {"age": 30, "name": "szx"} | 2 | | {"age": 35, "name": "wwj"} | 3 | +-----------------------------+----+ 3 rows in set (0.00 sec)
json_valid()
json_valid
可以判断字段是否是json类型,如果是则返回1 不是返回0
mysql> select json_valid(aley) from test; +------------------+ | json_valid(aley) | +------------------+ | 1 | | 1 | | 1 | +------------------+ 3 rows in set (0.00 sec) mysql> select json_valid(9); +---------------+ | json_valid(9) | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec)
json_extract()
使用json_extract
获取json内的数据,json_extract
需要两个参数, 第一个参数是字段名,第二个参数是需要取的json值,$表示根节点,$.age就是根节点下的age值,如果是多层json可以一直接着后面.key
mysql> select json_extract(aley, "$.age") as age from test; +------+ | age | +------+ | 18 | | 30 | | 35 | +------+ 3 rows in set (0.00 sec)
json_keys()
使用json_keys
可以查看最上层的所有key, 如果是多层的json只会返回传入的最上层的key
mysql> select json_keys(aley) from test; +-----------------+ | json_keys(aley) | +-----------------+ | ["age", "name"] | | ["age", "name"] | | ["age", "name"] | +-----------------+ 3 rows in set (0.01 sec) mysql> select json_keys('{"a":{"b":1}}'); +----------------------------+ | json_keys('{"a":{"b":1}}') | +----------------------------+ | ["a"] | +----------------------------+ 1 row in set (0.00 sec) mysql> select json_keys(json_extract('{"a":{"b":1}}', '$.a')); +-------------------------------------------------+ | json_keys(json_extract('{"a":{"b":1}}', '$.a')) | +-------------------------------------------------+ | ["b"] | +-------------------------------------------------+ 1 row in set (0.02 sec)
json_type()
使用json_type
可以查看类型
mysql> select json_type('[1,2,3]'); +----------------------+ | json_type('[1,2,3]') | +----------------------+ | ARRAY | +----------------------+ 1 row in set (0.00 sec) mysql> select json_type('{"a":1}'); +----------------------+ | json_type('{"a":1}') | +----------------------+ | OBJECT | +----------------------+ 1 row in set (0.00 sec) mysql> select json_type('"a"'); +------------------+ | json_type('"a"') | +------------------+ | STRING | +------------------+ 1 row in set (0.00 sec)
json_array()
使用json_array
可以获得一个json数组,传入一个空的或者多个值,返回这些值的json数组
mysql> select json_array("a", "b", now()); +------------------------------------------+ | json_array("a", "b", now()) | +------------------------------------------+ | ["a", "b", "2021-06-08 10:36:50.000000"] | +------------------------------------------+ 1 row in set (0.00 sec)
json_extract
使用json_extract
可以获取json里面的值
mysql> select json_extract(aley, '$.name') from test; +------------------------------+ | json_extract(aley, '$.name') | +------------------------------+ | "aley" | | "szx" | | "wwj" | +------------------------------+ 3 rows in set (0.00 sec)
注: json_extract 第一个参数是json数据, 第二个参数是取json值的路径, $
为根节点。后面跟json的键(例:json为 {"a":1}, 要取a的值 $.a)
如果是多层嵌套的json可以接着点,如果是json的值是数组可以在键后面跟索引取对应的值
mysql> select json_extract('{"a": [1,2,3]}', '$.a'); +---------------------------------------+ | json_extract('{"a": [1,2,3]}', '$.a') | +---------------------------------------+ | [1, 2, 3] | +---------------------------------------+ 1 row in set (0.00 sec) mysql> select json_extract('{"a": [1,2,3]}', '$.a[*]'); +------------------------------------------+ | json_extract('{"a": [1,2,3]}', '$.a[*]') | +------------------------------------------+ | [1, 2, 3] | +------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_extract('{"a": [1,2,3]}', '$.a[0]'); +------------------------------------------+ | json_extract('{"a": [1,2,3]}', '$.a[0]') | +------------------------------------------+ | 1 | +------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_extract('{"a": [1,2,3]}', '$.a[1]'); +------------------------------------------+ | json_extract('{"a": [1,2,3]}', '$.a[1]') | +------------------------------------------+ | 2 | +------------------------------------------+ 1 row in set (0.00 sec)
这篇关于mysql(一) json数据类型的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-09vue3开发前端表单缓存自定义指令,移动端h5必备插件
- 2024-05-09React Hooks在class组件中的使用方式
- 2024-03-30[OIDC in Action] 2. 基于OIDC(OpenID Connect)的SSO(纯JS客户端)
- 2024-03-29terraform jsonencode
- 2024-03-13vuex-persist
- 2024-03-11icons for vue
- 2024-03-07breadcrumbs react js
- 2024-03-06react login page example
- 2024-03-06react router uselocation
- 2024-03-04postgres jsonb_set