MySQL 的 xml 和 json 支持

MySQL 的 xml 和 json 支持

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 测试 MySQL 5.6 MySQL 8.0 通过
create table xml(
id int not null auto_increment,
xml varchar(255) not null,
primary key(id)
);

insert into xml(xml) values('<sucess>100</sucess>');
insert into xml(xml) values('<sucess>100</sucess><failure>200</failure>');

select id, extractvalue(xml, 'sucess') as sucess from xml;

+----+--------+
| id | sucess |
+----+--------+
| 1 | 100 |
| 2 | 100 |
+----+--------+
1 row in set (0.00 sec)

select id, extractvalue(xml, 'sucess') as sucess, extractvalue(xml, 'failure') as failure from xml;
+----+--------+---------+
| id | sucess | failure |
+----+--------+---------+
| 1 | 100 | |
| 2 | 100 | 200 |
+----+--------+---------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 测试 MySQL 8.0 通过
create table json(
id int not null auto_increment,
json varchar(255) not null,
primary key(id)
);


insert into json(json) values('{"name": "Alice", "sex": "female", "age": 23}');
insert into json(json) values('{"name": "Bob", "sex": "male", "age": 20}');

select json_extract(json, '$.name') as name, json_extract(json, '$.sex') as sex, json_extract(json, '$.age') as age from json;
+---------+----------+------+
| name | sex | age |
+---------+----------+------+
| "Alice" | "female" | 23 |
| "Bob" | "male" | 20 |
+---------+----------+------+
2 rows in set (0.00 sec)