Mysql 模糊查询的几种方式

查询方式

LIKE

SELECT * FROM 表名 WHERE 字段名 like "%字符%";

find_in_set(要查的字符, 表字段名)

SELECT * FROM 表名 WHERE find_in_set('字符', 字段名);

locate(要查的字符,表字段名)

SELECT * FROM 表名 WHERE locate('字符', 字段名);

INSTR(表字段名,要查的字符)

select * from 表名 where INSTR(字段,字符);

查询效率

我有张表，数据不多，我想查出表字段service_project_ids含有25的结果

mysql> SELECT id,name,service_project_ids FROM `laifu`.`laifu_service_diseases&problems`;
+----+-----------------+---------------------+
| id | name            | service_project_ids |
+----+-----------------+---------------------+
|  1 | 心血管疾病      | 25                  |
|  2 | 肝病            | 2,25,3              |
|  5 | 胃病            | 3,4,25              |
|  6 | 皮肤病          | 4,5,235             |
|  7 | 肠胃病          | 5,1256              |
+----+-----------------+---------------------+
5 rows in set (0.00 sec)

以下分别是不同查询语句的结果，以及查询时间

Like

mysql> select id,name,service_project_ids FROM `laifu_service_diseases&problems` where  service_project_ids LIKE '25' OR service_project_ids LIKE '25,%' 
    -> OR service_project_ids LIKE '%,25,%' OR service_project_ids LIKE '%,25';
+----+-----------------+---------------------+
| id | name            | service_project_ids |
+----+-----------------+---------------------+
|  1 | 心血管疾病      | 25                  |
|  2 | 肝病            | 2,25,3              |
|  5 | 胃病            | 3,4,25              |
+----+-----------------+---------------------+
3 rows in set (0.000082 sec)

find_in_set()

mysql> select id,name,service_project_ids FROM `laifu_service_diseases&problems` where  find_in_set(25,service_project_ids);
+----+-----------------+---------------------+
| id | name            | service_project_ids |
+----+-----------------+---------------------+
|  1 | 心血管疾病      | 25                  |
|  2 | 肝病            | 2,25,3              |
|  5 | 胃病            | 3,4,25              |
+----+-----------------+---------------------+
3 rows in set (0.000086 sec)

locate()

mysql> select id,name,service_project_ids FROM `laifu_service_diseases&problems` where  locate(25,service_project_ids);
+----+-----------------+---------------------+
| id | name            | service_project_ids |
+----+-----------------+---------------------+
|  1 | 心血管疾病      | 25                  |
|  2 | 肝病            | 2,25,3              |
|  5 | 胃病            | 3,4,25              |
|  7 | 肠胃病          | 5,1256              |
+----+-----------------+---------------------+
4 rows in set (0.000592 sec)

INSTR()

mysql> select id,name,service_project_ids FROM `laifu_service_diseases&problems` where  INSTR(service_project_ids,25);
+----+-----------------+---------------------+
| id | name            | service_project_ids |
+----+-----------------+---------------------+
|  1 | 心血管疾病      | 25                  |
|  2 | 肝病            | 2,25,3              |
|  5 | 胃病            | 3,4,25              |
|  7 | 肠胃病          | 5,1256              |
+----+-----------------+---------------------+
4 rows in set (0.000799 sec)

结论

对于小规模的模糊查询，使用LIKE依旧是最好的选择，而其他网页说大规模模糊查询时，locate却是性能最好的。

---

参考资料：

• MySQL如何判断字段是否包含某个字符串