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     |
---------------------------

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

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

Given the criteria POOLE the database returns:

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

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

---------------------------
|  Name  |       Town     |
---------------------------
| Name 2 | POOLE/WALLASEY |
| Name 3 | 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?

回答1:

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

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

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.

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

回答2:

This is an iteration of an often-asked class of questions: How do I select on a single datum, if I have more than one in a field?

The answer, as always, is: You don't.

There are many reasons for that, but one of the most important is performance: Basically a LIKE '%...' can't use an index. That might be ok with a handful of test rows, but it quickly becomes a problem when scaling.

The only reliable ways are to

• either normalize your data
• or use a fulltext index

In your case I'd strongly vote for normalization: Create a towns table, then link it to the players via a join table. You can now search for any town with full index use, finding the players through the join.

回答3:

As Marc B stated, using explode.

<?php
$array = explode("/",$global->getPlayerTown());

foreach($array as $Town){
  $list = $list ."'%" .$Town ."%', ";
}

$SQL = "SELECT * FROM `table` WHERE `Town` LIKE ANY(" .$list .")";
?>

Please go the smart route and normalize your data. This idea may work, but that doesn't mean it is the best choice.

回答4:

You could explode the towns, then loop through them and build the query like so:

$towns = explode('/', $global->getPlayerTown());
$first = true;
$like_sql = '';
foreach($towns as $town) {
    $like_sql .= $first ? ' WHERE ' : ' OR ';
    $like_sql .= "`Town` LIKE '%{$town}%'";
    $first = false;
}
$query = "SELECT * FROM `table` {$like_sql}";

However I would recommend you normalise your data, and have a separate towns table, with a user_town pivot table.