MySQLi WHERE LIKE多个条件 [英] MySQLi WHERE LIKE multiple criteria

问题描述

我有以下示例表和属性:

I have the following example table and attributes:

---------------------------
|  Name  |       Town     |
---------------------------
| Name 1 |      POOLE     |
| Name 2 | POOLE/WALLASEY |
| Name 3 | POOLE/WALLASEY |
| Name 4 |      POOLE     |
---------------------------

我正在PHP中使用以下SQL语句来检索行:

I am using the following SQL statement in PHP to retrieve rows:

SELECT * FROM `table` WHERE `Town` LIKE '%".$global->getPlayerTown()."%'

鉴于条件POOLE数据库返回:

---------------------------
|  Name  |       Town     |
---------------------------
| Name 1 |      POOLE     |
| Name 2 | POOLE/WALLASEY |
| Name 3 | POOLE/WALLASEY |
| Name 4 |      POOLE     |
---------------------------

但是，当使用条件POOLE/WALLASEY时，查询将返回:

However when using the criteria POOLE/WALLASEY the query returns:

---------------------------
|  Name  |       Town     |
---------------------------
| Name 2 | POOLE/WALLASEY |
| Name 3 | POOLE/WALLASEY |
---------------------------

我如何聪明地告诉PHP在一个查询中将字符串分成单独的条件(即POOLE和WALLASEY)，以便查询检索所有行?

How do I intelligently tell the PHP to split the string into separate criteria (i.e. POOLE and WALLASEY) in one query, so that the query retrieves all rows?

推荐答案

SELECT * FROM `table` WHERE `town` REGEXP 'POOLE|WALLASEY';

这将匹配具有一个或多个POOLE或WALLASEY实例的任何行.

This will match any rows that has one or more instances of POOLE or WALLASEY.

在PHP方面，取决于数据集中有多少种分隔符(在本例中为'/')，它很快就会变得很混乱. 但是用'|'代替'/'在getPlayerTown()中似乎是一种方法.

As to the PHP side, depending on how many kinds of separators ('/' in this case) you have in your dataset, it can get rather messy rather quickly. But replace '/' with '|' in getPlayerTown() would seem to be one way of doing it.

关于性能，我不确定REGEXP与LIKE相比如何.

As to performance, I'm not sure how REGEXP is as opposed to LIKE.

https://dev.mysql.com/doc/refman/5.7/en/regexp.html

这篇关于MySQLi WHERE LIKE多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！