用宽恕搜索数据库。

I have a database of 30k elements, each game names.

I'm currently using:

SELECT * from gamelist WHERE name LIKE '%%%$search%%%' OR aliases LIKE '%$search%' LIMIT 10"

for the searchbar.
However, it can get really picky about things like:

'Animal Crossing: Wild World' 

See, many users won't know that ':' is in that phrase, so when they start searching:

 Animal Crossing Wild World 

It won't show up.

How can I have the sql be more forgiving?

Replace the non alphanumeric characters in the search parameter with wildcards so Animal Crossing Wild World becomes %Animal%Crossing%Wild%World% and filter on that.

The following is from MySQL LIKE %string% not quite forgiving enough. Anything else I can use? by the user M_M:

If you're using MyISAM, you can use full text indexing. See this tutorial

If you're using a different storage engine, you could use a third party full text engine like sphinx, which can act as a storage engine for mysql or a separate server that can be queried.

With MySQL full text indexing a search on A J Kelly would match AJ Kelly (no to confuse matters but A, J and AJ would be ignored as they are too short by default and it would match on Kelly.) Generally Fulltext is much more forgiving (and usually faster than LIKE '%string%') because allows partial matches which can then be ranked on relevance.

You can also use SOUNDEX to make searches more forgiving by indexing the phonetic equivalents of words and search them by applying SOUNDEX on your search terms and then using those to search the index. With soundex mary, marie, and marry will all match, for example.

I would suggest you make another table witch contains keyworks like

+---------+------------+
| game_id | keyword_id |
+---------+------------+
|       1 |          1 |
|       1 |          2 |
|       1 |          3 |
|       1 |          4 |
+---------+------------+

+------------+--------------+
| keyword_id | keyword_name |
+------------+--------------+
|          1 | animal       |
|          2 | crossing     |
|          3 | wild         |
|          4 | world        |
+------------+--------------+

After that you can easily explode the user given text into keywords and search for them in the database, witch will give you the id's of the possible games he/she was looking for.

Oh, and remove special symbols, like ":" or "-", so you don't need multiple keywords for the same phrase.

You can try Match () AGAINST () if your MySQL engine is MyISAM or InnoDB:

http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

Your resulting SQL will be like this:

SELECT * from gamelist WHERE MATCH (name, aliases) AGAINST ('$search' IN BOOLEAN MODE) LIMIT 10

The behavior of the search is more like the boolean search used in search engines.