0%

MySQL函数concat多个字段模糊查询不匹配问题

发表于 更新于 Disqus：
本文字数： 6.5k 阅读时长 ≈ 11 分钟

MySQL函数concat多个字段模糊查询不匹配问题

表结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `user_address` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` char(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '姓名',
  `phone` char(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '电话',
  `zip_code` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '邮编',
  `address_district` int DEFAULT '0' COMMENT '地址-区',
  `address_road` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '地址-街道/路',
  `address_detail` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '详细地址',
  `user_id` int DEFAULT '0' COMMENT '用户id',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `status` tinyint DEFAULT '1' COMMENT '状态(1有效,-1删除)',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='收货人地址表';
表数据
1
2
INSERT INTO `publisher`.`user_address`(`id`, `name`, `phone`, `zip_code`, `address_district`, `address_road`, `address_detail`, `user_id`, `create_time`, `update_time`, `status`) VALUES (8, '王林', '13061111111', '261234', 6, '永清路', '173号', 12, NULL, '2022-03-09 17:23:35', 1);
INSERT INTO `publisher`.`user_address`(`id`, `name`, `phone`, `zip_code`, `address_district`, `address_road`, `address_detail`, `user_id`, `create_time`, `update_time`, `status`) VALUES (16, '徐三', '13071111111', NULL, 3, '永清路', NULL, 6, NULL, '2022-03-11 10:28:20', 1);
查询语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT * FROM user_address WHERE CONCAT(address_road, address_detail) LIKE '%永清路%'\G
*************************** 1. row ***************************
              id: 8
            name: 王林
           phone: 13061111111
        zip_code: 261234
address_district: 6
    address_road: 永清路
  address_detail: 173号
         user_id: 12
     create_time: NULL
     update_time: 2022-03-09 17:23:35
          status: 1
1 row in set (0.00 sec)
具体原因

MySQL :: Reference Manual :: 12.8 String Functions and Operators

CONCAT() returns NULL if any argument is NULL.

1
2
3
4
mysql> SELECT CONCAT('My', NULL, 'QL')\G
*************************** 1. row ***************************
CONCAT('My', NULL, 'QL'): NULL
1 row in set (0.00 sec)
解决方案一：

将 address_detail 字段的默认值由 NULL 改为 Empty String。

解决方案二：

  1. 使用 IFNULL 优化查询语句

    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
    mysql>  SELECT * FROM user_address WHERE CONCAT(address_road, IFNULL(address_detail, "")) LIKE '%永清路%'\G
    *************************** 1. row ***************************
                  id: 8
                name: 王林
               phone: 13061111111
            zip_code: 261234
    address_district: 6
        address_road: 永清路
      address_detail: 173号
             user_id: 12
         create_time: NULL
         update_time: 2022-03-09 17:23:35
              status: 1
    *************************** 2. row ***************************
                  id: 16
                name: 徐三
               phone: 13071111111
            zip_code: NULL
    address_district: 3
        address_road: 永清路
      address_detail: NULL
             user_id: 6
         create_time: NULL
         update_time: 2022-03-11 10:28:20
              status: 1
    2 rows in set (0.01 sec)

  1. 将 concat 改为 concat_sw

    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
    mysql> SELECT * FROM user_address WHERE CONCAT_WS('', address_road, address_detail) LIKE '%永清路%'\G
    *************************** 1. row ***************************
                  id: 8
                name: 王林
               phone: 13061111111
            zip_code: 261234
    address_district: 6
        address_road: 永清路
      address_detail: 173号
             user_id: 12
         create_time: NULL
         update_time: 2022-03-09 17:23:35
              status: 1
    *************************** 2. row ***************************
                  id: 16
                name: 徐三
               phone: 13071111111
            zip_code: NULL
    address_district: 3
        address_road: 永清路
      address_detail: NULL
             user_id: 6
         create_time: NULL
         update_time: 2022-03-11 10:28:20
              status: 1
    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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
mysql> SELECT USER.*
    -> FROM
    ->     USER left JOIN user_address ON USER.id = user_address.user_id
    ->     left JOIN area ON area.id = user_address.address_district
    -> WHERE
    ->     USER.is_deleted = '0'
    ->     AND ( USER.username LIKE '%徐三%' OR ( user_address.NAME LIKE '%徐三%' AND user_address.STATUS = '1' ) ) \G
*************************** 1. row ***************************
               id: 6
         username: test2
         password: null
       avatar_url: fcd57e79f45c488c82bcc52b8b6c6139.png
         nickname: abcdefjhijklmnopqrsquvwxyzabcdefjhijklmnopqrsquvwx
            phone: 1831333221
            email: test@163.com
               ip:
             port: 0
     audit_status: 10
audit_sys_user_id: 2
     audit_reason: 2222
       is_deleted: 0
      create_time: 2022-02-21 10:43:16
      update_time: 2022-03-10 17:06:56
*************************** 2. row ***************************
               id: 22
         username: 徐三
         password:
       avatar_url:
         nickname:
            phone: 1831333222
            email: test@aflaksdjlfasdlfkjasldfjaldjflasdkjflkajdlfjalsdkjflagklah.commm
               ip:
             port: 0
     audit_status: 0
audit_sys_user_id: 0
     audit_reason: NULL
       is_deleted: 0
      create_time: 2022-02-22 11:16:17
      update_time: 2022-02-22 11:16:17
*************************** 3. row ***************************
               id: 22
         username: 徐三
         password:
       avatar_url:
         nickname:
            phone: 1831333222
            email: test@aflaksdjlfasdlfkjasldfjaldjflasdkjflkajdlfjalsdkjflagklah.commm
               ip:
             port: 0
     audit_status: 0
audit_sys_user_id: 0
     audit_reason: NULL
       is_deleted: 0
      create_time: 2022-02-22 11:16:17
      update_time: 2022-02-22 11:16:17
*************************** 4. row ***************************
               id: 22
         username: 徐三
         password:
       avatar_url:
         nickname:
            phone: 1831333222
            email: test@aflaksdjlfasdlfkjasldfjaldjflasdkjflkajdlfjalsdkjflagklah.commm
               ip:
             port: 0
     audit_status: 0
audit_sys_user_id: 0
     audit_reason: NULL
       is_deleted: 0
      create_time: 2022-02-22 11:16:17
      update_time: 2022-02-22 11:16:17
*************************** 5. row ***************************
               id: 22
         username: 徐三
         password:
       avatar_url:
         nickname:
            phone: 1831333222
            email: test@aflaksdjlfasdlfkjasldfjaldjflasdkjflkajdlfjalsdkjflagklah.commm
               ip:
             port: 0
     audit_status: 0
audit_sys_user_id: 0
     audit_reason: NULL
       is_deleted: 0
      create_time: 2022-02-22 11:16:17
      update_time: 2022-02-22 11:16:17
*************************** 6. row ***************************
               id: 22
         username: 徐三
         password:
       avatar_url:
         nickname:
            phone: 1831333222
            email: test@aflaksdjlfasdlfkjasldfjaldjflasdkjflkajdlfjalsdkjflagklah.commm
               ip:
             port: 0
     audit_status: 0
audit_sys_user_id: 0
     audit_reason: NULL
       is_deleted: 0
      create_time: 2022-02-22 11:16:17
      update_time: 2022-02-22 11:16:17
6 rows in set (0.01 sec)