mysql Concat与OR的区别

Question

我试图找到搜索多列中单词出现的最佳方法。当然，一种选择是在查询中添加多个OR：

SELECT * FROM table WHERE column1 LIKE %term1% OR column2 LIKE %term1% OR......

尽管如此，当有多个条款时，我认为这使查询变得相当长。

在我的搜索中，我发现了MYSQL中的CONCAT函数。这似乎允许我在连接列后进行一次搜索（对吗？）：

SELECT * FROM table WHERE CONCAT(column1,column2,....) LIKE %term1% OR CONCAT(column 1,column2,...) LIKE %term2% ....

这样做的优点是更易于阅读和维护。

因为我还不完全理解 CONCAT 的全部用途，所以我不知道是否这种方法是正确的。我想知道实现这种搜索的最正确和最有效的方法是什么。（注：我还计划按照这个 stackoverflow 问题中所述对其进行排序：mysql SORT BY amount of unique word matches，也许在使用该方法时存在问题？）

编辑：

也许直接解释我的最终愿望会更容易：在 PHP 中，我有一个单词数组。我希望选择所有在多列中出现过其中一个单词的数据库行，然后根据相关性对它们进行排序（甚至给某些列添加额外的重要性）。 （请注意，只需要搜索少于5000行数据）

- PSZ_Code

---

有趣。在任何情况下，查找匹配行都会很慢（因为LIKE中的前导通配符不会使用索引）。这可能意味着对长字符串执行单个LIKE可能会更快。缺点是，如果您搜索ME一词，则还会找到列1以M结尾且列2以E开头的行。 - Kickstart

@Kickstart：如果通配符被跳过了怎么办？使用concat_ws可以解决ME问题，不是吗？ - PSZ_Code

Concat_ws可以解决问题，直到用户搜索包含您选择使用的分隔符的字符串。前导通配符的问题是MySQL无法使用索引找到它们（可能可以使用尾随通配符）。如果没有前导通配符，则使用许多OR将允许使用索引，但将字段连接在一起将确保仍需要前导通配符。 - Kickstart

在您的帖子末尾按相关性排序会立即将“全文”作为解决方案。这就是它的目的。但是，如果您无法使用它，因为您的InnoDB版本不支持它 - 那么，好吧，最好的建议：将MySQL服务器升级到5.6。否则，您将始终围绕此类神秘解决方案。从性能的角度来看，OR LIKE可能会使用索引，但这并不保证，在常见情况下，它不会使用索引。因此，两种方法都不好，唯一重要的事情可能是可读性。这是您需要做出的决定。 - Alma Do

我无法更新MySQL服务器。 - PSZ_Code

Answer 1

我认为这是一个有趣的观点，所以最好还是试一试。

嗯，在您编辑之后，似乎您没有那么多数据，但我仍然会发布结果：

---

编辑：这是我第一次尝试MySQL测试，正如@Alma Do所说，这些结果“可能只是波动”。

---

第一个查询（在90000行上）road和road2均为VARCHAR（100）：

SELECT * FROM adress WHERE CONCAT(road1,road2) LIKE '%test%'; 0.0503秒

SELECT * FROM adress WHERE road1 LIKE '%test%' OR road2 LIKE '%test%'; 0.0710秒

看起来确实使用多个LIKE更具资源效益。我对MySQL资源验证不是很擅长，所以我没有进一步研究，应该还是会给您提供一些想法。

我看到的主要问题是CONCAT是否可以创建错误匹配：

SELECT CONCAT('fooa','bfoo') with LIKE '%ab%' would work, while 'fooa' LIKE '%ab% OR 'bfoo' LIKE '%ab% OR wouldn't.

对于500行，我认为您不会遇到资源问题，所以我会选择“更易于阅读和维护”的方法。

希望这有所帮助。

- Meeuuuhhhh

---

这些测量值只是几乎没有意义的数字。实际情况可能取决于许多情况。例如，第一列可能始终包含所需单词，因此第二个 LIKE 将永远不会被执行（短路）。或者，如果第一列不包含该单词，则每行都将执行两个 LIKE。唯一正确的点是 - 单词重叠边缘情况，但可以通过使用分隔符来修复。然而，90,000 也只是在测试结果中严肃考虑的太少了。0.01..0.05 秒可能只是波动。 - Alma Do

谢谢你的建议，这是我第一次尝试进行MySQL测试，想试试看自己能否做些什么，但确实，很多细节开始成为问题...我相信它们可能并不那么精确，但希望它仍然有所帮助 :) - Meeuuuhhhh

对我来说足够好 - Martin Zvarík

点赞给“具有更易于阅读和维护的优势”！ - Herbert Van-Vliet

顺便说一下：看看能否使用CONCAT_WS而不是CONCAT。 - Herbert Van-Vliet

Answer 2

最好的方法是使用全文搜索引擎来执行该任务，或使用MySQL的全文本功能。

您可以在此处找到更多关于它的解释： 全文自然语言

- Tata

---

但是如果使用INNOdb表并且不确定是否正在使用mysql 5.6，则无法使用此功能。 - PSZ_Code

在这种情况下，请使用外部全文搜索引擎。 - Tata

似乎我还没有听说过那个。你能解释一下吗？ - PSZ_Code

你可以使用外部的全文搜索引擎，比如Solr、Sphinx或Elasticsearch。然后，你应该定义查询语句，通过使用一个关键字每隔X分钟从数据库中提取数据，并在这些搜索引擎中执行所有的搜索操作，而不是在MySQL中进行。 - Tata