#Issue# 联表多字段的模糊查询tricks
Issue
5 minutes read (About 689 words) 0 visits

#Issue# 联表多字段的模糊查询tricks

本文讨论联表多字段模糊查询的可能性，分为两种思路：

  1. 交给 dao layer 处理
  2. 交给 service layer 处理

需求

今天讨论一下看似简单实则繁琐的小需求——联表多字段的模糊查询。举个🌰：现在我要对超级管理员列表进行名字或描述的模糊查询，怎么实现？这里阻碍我们短平快地实现功能的点在于，超级管理员涉及三个表（用户表、角色表、用户-角色表），每个表还可能对多字段进行精确或模糊匹配，如果不先想清楚就开始动工，非常容易出现功能缺陷和性能瓶颈🤦‍。

实现

交给 dao layer 处理

MySQL (Rational)

联表查询

1
2
3
SELECT * FROM `user_role` AS `ur` 
FULL JOIN `users` on ur.user_id = users.user_id
FULL JOIN `roles` on ur.role_id = roles.role_id

模糊查询

1
SELECT * FROM `users` CONCAT(IFNULL(`user_name`,''),IFNULL(`user_desc`,'')) LIKE '%keyword%'

联表多字段的模糊查询

1
2
3
4
5
6
# tips：满足关系型的数据使用联表多字段的模糊查询可做到 SQL or ORM 一把梭
SELECT * FROM `user_role` AS `ur` 
FULL JOIN `users` on ur.user_id = users.user_id
FULL JOIN `roles` on ur.role_id = roles.role_id
WHERE role_name=admin
CONCAT(IFNULL(`user_name`,''),IFNULL(`user_desc`,'')) LIKE ‘%keyword%’

MongoDB (Irrational)

联表查询

1
2
3
4
5
6
7
8
db.users.aggregate({
    "$lookup": {
        from:'roles',
        localField: 'role_id',
        foreignField: '_id',
        as: 'user_role'
    }
})

模糊查询

1
2
3
4
5
6
7
8
9
10
db.users.find({
    "$or": [
        "user_name": {
    		"$regex": "keyword"
		},
        "user_desc": {
        	"$regex": "keyword"
        }
    ]
})

联表多字段的模糊查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// tips：不推荐非关系型用联表方法因为性能十分堪忧，更应该关注合理的数据结构设计
db.users.aggregate({
    "$lookup": {
        from:'roles',
        localField: 'role_id',
        foreignField: '_id',
        as: 'user_role'
    }
}, {
    "$match": {
        "$or": [
            "user_name": {
                "$regex": "keyword"
            },
            "user_desc": {
                "$regex": "keyword"
            }
        ]
    }             
})

交给 service layer 处理

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
39
40
41
42
43
44
45
46
47
// 数据库 I/O：3 times
// 空间复杂度：O(k)
// 时间复杂度：O(k × f)
// 其中，k 代表模糊搜索后的记录数量，f 代表条件过滤后的记录数量，k 和 f 在一般情况下是很小的
func FuzzySearch(keyword string) ([]rbac.User, error) {
    var result []rbac.User

    // 模糊搜索
    // 空间复杂度↓，数据库 I/O ++
    users, err := rbac.GetUserList(keyword)
    if err != nil {
        return nil, err
    }
    // 时间复杂度↑，O(k)
    var userIds []string
    for _, user := range users {
        userIds = append(userIds, user.UserId)
    }
    
    // 精准匹配
    // 空间复杂度=，数据库 I/O ++
    role, err := rbac.GetRoleDetail(RoleNameAdmin)
    if err != nil {
        return nil, err
    }
    // 时间复杂度↑，O(1)
    var roleIds []string
    roleIds = append(roleIds, role.RoleId)

    // 条件过滤
    // 空间复杂度↓，数据库 I/O ++
    urs, err := rbac.GetUserRoleList(userIds, roleIds)
    if err != nil {
        return nil, err
    }
    // 时间复杂度↑，O(k × f)
    for _, user := range users {
        for _, ur := range urs {
            if user.UserId == ur.UserId {
                result = append(result, user)
                break
            }
        }
    }

    return result, nil
}
# Golang
#JavaScript# 思维导图：JavaScript
#Issue# Best Practice on Token-Refresh Implementation

Comments

Follow

Categories

Archives

Recent

Tag Cloud

Concurrency Docker Fiddler Gin Git GitHub Pages Go-Micro GoConvey GoMock GoMonkey Golang Gorm Grpc Hexo JavaScript Kubernetes Linux Microservice Mock MongoDB Newman Postman Programming Paradigm PromQL Pytest Redis SQL Shell Swagger TypeScript Vim WSL

Tags

Concurrency 6
Docker 3
Fiddler 1
Gin 3
Git 1
GitHub Pages 1
Go-Micro 1
GoConvey 1
GoMock 1
GoMonkey 1
Golang 19
Gorm 1
Grpc 1
Hexo 1
JavaScript 1
Kubernetes 3
Linux 8
Microservice 12
Mock 3
MongoDB 1
Newman 1
Postman 1
Programming Paradigm 2
PromQL 1
Pytest 1
Redis 1
SQL 1
Shell 3
Swagger 1
TypeScript 1
Vim 1
WSL 4

Links

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×