3
4
5
6
7
8
9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
2
3
2
4
2
5
2
6
2
7
2
| Field
| Type
|
Null
|
Key
|
Default
| Extra |
+
---------+------------------+------+-----+---------+-------+
| id
|
int
(10) unsigned |
NO
| PRI |
NULL
|
|
| context | json
| YES
|
|
NULL
|
|
+
---------+------------------+------+-----+---------+-------+
2
rows
in
set
(0.00 sec)
mysql>
insert into
helei
values
(1,
'{"name":"贺磊
","age":100}'
),(2,
'{"name":"陈加持","age":30}'
),(3,
'{"name":"于浩
","age":28}'
);
Query OK, 3
rows
affected (0.00 sec)
Records: 3
Duplicates: 0
Warnings: 0
mysql>
select
*
from
helei;
+
----+----------------------------------+
| id | context
|
+
----+----------------------------------+
|
1 | {
"age"
: 100,
"name"
:
"贺磊"
}
|
|
2 | {
"age"
: 30,
"name"
:
"陈加持"
}
|
|
3 | {
"age"
: 28,
"name"
:
"于浩"
}
|
+
----+----------------------------------+
3
rows
in
set
(0.00 sec)
mysql>
select
id,JSON_EXTRACT(context,
'$.name'
)
name
,JSON_EXTRACT(cont
ext,
'$.age'
) age
from
helei;
+
----+-------------+------+
| id |
name
| age
|
+
----+-------------+------+
|
1 |
"贺磊"
| 100
|
|
2 |
"陈加持"
| 30
|
|
3 |
"于浩"
| 28
|
+
----+-------------+------+
3
rows
in
set
(0.00 sec)
获取
Key
-Value
mysql>
select
id,json_keys(context)
from
helei;
+
----+--------------------+
| id | json_keys(context) |
+
----+--------------------+
|
1 | [
"age"
,
"name"
]
|
|
2 | [
"age"
,
"name"
]
|
|
3 | [
"age"
,
"name"
]
|
+
----+--------------------+