MySQL 哪个 SQL 查询更好，MATCH AGAINST 还是 LIKE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/792875/
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
Which SQL query is better, MATCH AGAINST or LIKE?
提问by Wil
To search the database for rows that have both keywords "foo" AND "bar" in any of the columns "foo_desc" and "bar_desc", I would do something like:
要在数据库中搜索在任何列“foo_desc”和“bar_desc”中同时具有关键字“foo”和“bar”的行，我会执行以下操作：
SELECT *
FROM t1
WHERE MATCH (t1.foo_desc, t2.bar_desc) AGAINST ('+foo* +bar*' IN BOOLEAN MODE)
or
或者
SELECT *
FROM t1
WHERE (CONCAT(t1.foo_desc, t2.bar_desc) LIKE '%foo%') AND (CONCAT(t1.foo_desc, t2.bar_desc) LIKE '%bar%')
I expect the downside of the last query is performance.
我预计最后一个查询的缺点是性能。
The upside is that the LIKE query finds 'xxfoo' where MATCH AGAINST does not.
好处是 LIKE 查询找到了“xxfoo”，而 MATCH AGAINST 没有找到。
Which is the preferred one or is there a better solution?
哪个是首选或有更好的解决方案？
采纳答案by cletus
Update
更新
As of MySQL 5.6and later, InnoDBtables supports Match... Against.
从那时起MySQL 5.6，InnoDB表支持Match... Against.
The first is muchbetter. On MyISAMtables it will use a full text index against those columns. The other will do a full table scan doing a concat on every row and then a comparison.
第一个要好得多。在MyISAM表上，它将对这些列使用全文索引。另一个将进行全表扫描，对每一行进行连接，然后进行比较。
LIKEis only efficient if you're doing it against:
LIKE仅当您针对以下对象进行操作时才有效：
- a column (not a result of a function unless your particular database vendor supports functional indexes--Oracle, for example--and you're using them);
- the start of the column (ie
LIKE 'blah%'as opposed toLIKE '%blah%'); and - a column that's indexed.
- 一列（不是函数的结果，除非您的特定数据库供应商支持函数索引——例如 Oracle——并且您正在使用它们）；
- 列的开头（即
LIKE 'blah%'相对于LIKE '%blah%'）；和 - 已编入索引的列。
If any one of those conditions are not true the only way for the SQL engine to execute the query is by doing a full table scan. This can be usable under about 10-20 thousand rows. Beyond that it quickly becomes unusable however.
如果其中任何一个条件不成立，SQL 引擎执行查询的唯一方法就是执行全表扫描。这可以在大约 10-20,000 行下使用。然而，除此之外它很快变得无法使用。
Note:One problem with MATCH on MySQL is that it seems to only match against whole words so a search for 'bla' won't match a column with a value of 'blah', but a search for 'bla*' will.
注意：MySQL 上 MATCH 的一个问题是它似乎只匹配整个单词，因此搜索 'bla' 不会匹配值为 'blah' 的列，但搜索 'bla*' 会匹配。
