MySQL 的 xml 和 json 支持

发表于 分类于 阅读次数：
正在加载今日诗词.... 本文字数： 1.5k 阅读时长 ≈ 1 分钟

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)