haxianhe's blog

破壁MySQL - 其他问题

字数统计: 1.7k阅读时长: 7 min
2021/09/25 Share

JSON

在实际业务中经常会使用到 JSON 数据类型,在查询过程中主要有两种使用需求:

  1. 在 where 条件中有通过 json 中的某个字段去过滤返回结果的需求
  2. 查询 json 字段中的部分字段作为返回结果(减少内存占用)

JSON_CONTAINS

JSON_CONTAINS(target, candidate[, path])

如果在 json 字段 target 指定的位置 path,找到了目标值 condidate,返回 1,否则返回 0

如果只是检查在指定的路径是否存在数据,使用JSON_CONTAINS_PATH()

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
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 1 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
| 0 |
+-------------------------------+

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 0 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
| 1 |
+-------------------------------+

JSON_CONTAINS_PATH

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)

如果在指定的路径存在数据返回 1,否则返回 0

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
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
| 1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
| 0 |
+----------------------------------------+

实际使用:

1
2
3
4
5
6
7
8
$conds = new Criteria();
$conds->andWhere('dept_code', 'in', $deptCodes);
if (!empty($aoiAreaId)) {
$aoiAreaIdCond = new Criteria();
$aoiAreaIdCond->orWhere("JSON_CONTAINS_PATH(new_aoi_area_ids,'one', '$.\"$aoiAreaId\"')", '=', 1);
$aoiAreaIdCond->orWhere("JSON_CONTAINS_PATH(old_aoi_area_ids,'one', '$.\"$aoiAreaId\"')", '=', 1);
$conds->andWhere($aoiAreaIdCond);
}

column->path、column->>path

获取指定路径的值

-> vs ->>

Whereas the -> operator simply extracts a value, the ->> operator in addition unquotes the extracted result.

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
30
31
32
33
34
35
36
37
38
mysql> SELECT * FROM jemp WHERE g > 2;
+-------------------------------+------+
| c | g |
+-------------------------------+------+
| {"id": "3", "name": "Barney"} | 3 |
| {"id": "4", "name": "Betty"} | 4 |
+-------------------------------+------+
2 rows in set (0.01 sec)

mysql> SELECT c->'$.name' AS name
-> FROM jemp WHERE g > 2;
+----------+
| name |
+----------+
| "Barney" |
| "Betty" |
+----------+
2 rows in set (0.00 sec)

mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
-> FROM jemp WHERE g > 2;
+--------+
| name |
+--------+
| Barney |
| Betty |
+--------+
2 rows in set (0.00 sec)

mysql> SELECT c->>'$.name' AS name
-> FROM jemp WHERE g > 2;
+--------+
| name |
+--------+
| Barney |
| Betty |
+--------+
2 rows in set (0.00 sec)

实际使用:

1
$retTask = AoiAreaTaskOrm::findRows(['status', 'extra_info->>"$.new_aoi_area_infos" as new_aoi_area_infos', 'extra_info->>"$.old_aoi_area_infos" as old_aoi_area_infos'], $cond);

关系数据库设计理论

函数依赖

记 A->B 表示 A 函数决定 B,也可以说 B 函数依赖于 A。

如果 {A1,A2,… ,An} 是关系的一个或多个属性的集合,该集合函数决定了关系的其它所有属性并且是最小的,那么该集合就称为键码。

对于 A->B,如果能找到 A 的真子集 A’,使得 A’-> B,那么 A->B 就是部分函数依赖,否则就是完全函数依赖。

对于 A->B,B->C,则 A->C 是一个传递函数依赖

异常

以下的学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade},键码为 {Sno, Cname}。也就是说,确定学生和课程之后,就能确定其它信息。

Sno Sname Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 90
2 学生-2 学院-2 院长-2 课程-2 80
2 学生-2 学院-2 院长-2 课程-1 100
3 学生-3 学院-2 院长-2 课程-2 95

不符合范式的关系,会产生很多异常,主要有以下四种异常:

  • 冗余数据:例如 学生-2 出现了两次。
  • 修改异常:修改了一个记录中的信息,但是另一个记录中相同的信息却没有被修改。
  • 删除异常:删除一个信息,那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行,那么 学生-1 的信息就会丢失。
  • 插入异常:例如想要插入一个学生的信息,如果这个学生还没选课,那么就无法插入。

范式

范式理论是为了解决以上提到四种异常。

高级别范式的依赖于低级别的范式,1NF 是最低级别的范式。

第一范式 (1NF)

属性不可分。

第二范式 (2NF)

每个非主属性完全函数依赖于键码。

可以通过分解来满足。

分解前

Sno Sname Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 90
2 学生-2 学院-2 院长-2 课程-2 80
2 学生-2 学院-2 院长-2 课程-1 100
3 学生-3 学院-2 院长-2 课程-2 95

以上学生课程关系中,{Sno, Cname} 为键码,有如下函数依赖:

  • Sno -> Sname, Sdept
  • Sdept -> Mname
  • Sno, Cname-> Grade

Grade 完全函数依赖于键码,它没有任何冗余数据,每个学生的每门课都有特定的成绩。

Sname, Sdept 和 Mname 都部分依赖于键码,当一个学生选修了多门课时,这些数据就会出现多次,造成大量冗余数据。

分解后

关系-1

Sno Sname Sdept Mname
1 学生-1 学院-1 院长-1
2 学生-2 学院-2 院长-2
3 学生-3 学院-2 院长-2

有以下函数依赖:

  • Sno -> Sname, Sdept
  • Sdept -> Mname

关系-2

Sno Cname Grade
1 课程-1 90
2 课程-2 80
2 课程-1 100
3 课程-2 95

有以下函数依赖:

  • Sno, Cname -> Grade

第三范式 (3NF)

非主属性不传递函数依赖于键码。

上面的 关系-1 中存在以下传递函数依赖:

  • Sno -> Sdept -> Mname

可以进行以下分解:

关系-11

Sno Sname Sdept
1 学生-1 学院-1
2 学生-2 学院-2
3 学生-3 学院-2

关系-12

Sdept Mname
学院-1 院长-1
学院-2 院长-2

ER 图

Entity-Relationship,有三个组成部分:实体、属性、联系。

用来进行关系型数据库系统的概念设计。

实体的三种联系

包含一对一,一对多,多对多三种。

  • 如果 A 到 B 是一对多关系,那么画个带箭头的线段指向 B;
  • 如果是一对一,画两个带箭头的线段;
  • 如果是多对多,画两个不带箭头的线段。

下图的 Course 和 Student 是一对多的关系。

表示出现多次的关系

一个实体在联系出现几次,就要用几条线连接。

下图表示一个课程的先修关系,先修关系出现两个 Course 实体,第一个是先修课程,后一个是后修课程,因此需要用两条线来表示这种关系。

联系的多向性

虽然老师可以开设多门课,并且可以教授多名学生,但是对于特定的学生和课程,只有一个老师教授,这就构成了一个三元联系。

表示子类

用一个三角形和两条线来连接类和子类,与子类有关的属性和联系都连到子类上,而与父类和子类都有关的连到父类上。




CATALOG
  1. 1. JSON
    1. 1.1. JSON_CONTAINS
    2. 1.2. JSON_CONTAINS_PATH
    3. 1.3. column->path、column->>path
  2. 2. 关系数据库设计理论
    1. 2.1. 函数依赖
    2. 2.2. 异常
    3. 2.3. 范式
  3. 3. ER 图
    1. 3.1. 实体的三种联系
    2. 3.2. 表示出现多次的关系
    3. 3.3. 联系的多向性
    4. 3.4. 表示子类