MySQL 如何编写mysql查询来搜索数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5352106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write mysql query to search database?
提问by Dave
I'm own a wallpaper website and I'm trying to write a search feature that will search the database for the terms the user is searching for. I have 2 fields in the database I'm searching against TAGS and NAME
我拥有一个壁纸网站，我正在尝试编写一个搜索功能，该功能将在数据库中搜索用户正在搜索的术语。我在数据库中有 2 个字段，我正在针对 TAGS 和 NAME 进行搜索
The current way I'm doing it is I take the search term divide it up into multiple words and then search the database using those terms. So if a user searches for "New York" my query will look like this
我目前的做法是将搜索词分成多个词，然后使用这些词搜索数据库。因此，如果用户搜索“纽约”，我的查询将如下所示
SELECT * FROM wallpapers
WHERE tags LIKE '%New%' OR name LIKE '%new%'
or tags LIKE '%York%' OR name LIKE '%York%'
The issue with that of course is that anything with the term new in it will be pulled up also like say "new car" etc. If I replace the query above with the following code then it's too vague and only like 2 wallpapers will show up
当然，问题在于任何带有新术语的东西都会被拉起，比如“新车”等。如果我用下面的代码替换上面的查询，那么它太模糊了，只会显示 2 张壁纸
SELECT * FROM wallpapers
WHERE tags LIKE '%New York%' OR name LIKE '%New York%'
Does anyone have a better way to write a search query?
有没有人有更好的方法来编写搜索查询？
回答by Neville Kuyt
Looks like you want to introduce the concept of relevance.
看来您要介绍相关性的概念。
Try:
尝试：
select * from (
SELECT 1 as relevance, * FROM wallpapers
WHERE tags LIKE '%New York%' OR name LIKE '%New York%'
union
select 10 as relevance, * FROM wallpapers
WHERE (tags LIKE '%New%' OR name LIKE '%new%')
and (tags LIKE '%York%' OR name LIKE '%York%')
union
select 100 as relevance, * FROM wallpapers
WHERE tags LIKE '%New%' OR name LIKE '%new%'
union
select 100 as relevance, * FROM wallpapers
WHERE tags LIKE '%York%' OR name LIKE '%York%'
)
order by relevance asc
By the way, this will perform very, very poorly if your database grows too large - you want to be formatting your columns consistently so they're all upper case (or all lower case), and you want to avoid wildcards in your where clauses if you possibly can.
顺便说一句，如果您的数据库变得太大，这将非常非常糟糕 - 您希望一致地格式化您的列，以便它们全部为大写（或全部小写），并且您希望避免在您的 where 子句中使用通配符如果可以的话。
Once this becomes a problem, look at full text searching.
一旦这成为一个问题，请查看全文搜索。
回答by Lo?c Février
You can use your first query to do a first selection and after that your can rank the results : a wallpaper with both keywords "New" and "York" will be ranked higher than a wallpaper with only one of the keywords.
您可以使用您的第一个查询进行第一次选择，然后您可以对结果进行排名：同时包含关键字“New”和“York”的壁纸的排名将高于仅包含一个关键字的壁纸。
If the second query only returns 2 wallpaper, can you put some example of the tags/name of the wallpaper not returned by the query but that you would like to have ?
如果第二个查询只返回 2 个壁纸，您能否提供一些查询未返回但您想要的壁纸标签/名称示例？
Is it a problem of uppercase letters ? Spaces ?
是大写字母的问题吗？空间？
回答by Jai
I believe you may benefit from Full text indexes. Read up on mysql full text search. You will need to be using MyISAM engine for your tables.
我相信您可以从全文索引中受益。阅读 mysql 全文搜索。您将需要为您的表使用 MyISAM 引擎。
回答by Tino
Perhaps this is a really dumb question, but could be following possibly what you want?
也许这是一个非常愚蠢的问题，但可能会关注您想要的内容？
SELECT * FROM wallpapers
WHERE ( tags LIKE '%New%' OR name LIKE '%new%' )
and ( tags LIKE '%York%' OR name LIKE '%York%' )
This searches for wallpapers which must have both words but anywhere.
这将搜索必须同时包含两个词但在任何地方都包含的壁纸。
WarningBeware of SQL injection this way, when searching for "words" like new'yorkor new%york. Perhaps the most easy way is to treat all nonalpha/nonnumeric characters as spaces when splitting, such that new@yorkand similar becomes newand york.
警告在搜索“单词”（如new'york或 ）时，请注意以这种方式进行 SQL 注入new%york。也许最简单的方法是在拆分时将所有非字母/非数字字符视为空格，这样new@york和 类似就变成new和york。
Notes about searching:
搜索注意事项：
Searching this way in databases is plain overkill (full table scan). As long as you only have a few wallpapers this is not a bigger problem. Nearly all current cheap hardware should be able to search through a million wallpapers within a second or so, as long as the database fits into memory.
在数据库中以这种方式搜索显然是矫枉过正（全表扫描）。只要你只有几张壁纸，这不是什么大问题。只要数据库适合内存，几乎所有当前的廉价硬件都应该能够在一秒钟左右的时间内搜索一百万张壁纸。
However with bigger sites where the tags and name information exceeds available RAM, you certainly get a problem. Then it is time to try some other way to search. However what to do heavily depends on the expected use pattern, so to answer that more information is needed.
但是，对于标签和名称信息超过可用 RAM 的较大站点，您肯定会遇到问题。那么是时候尝试一些其他的搜索方式了。然而，做什么在很大程度上取决于预期的使用模式，因此需要更多信息来回答。
