在MySQL查询中使用“like”运算符，包括ColdFusion变量

Question

我正在构建一个“相关资源”页面，可以搜索与特定飞机有关的论文。如果访问者正在研究XV-1，我希望找到所有标题中包含“XV-1”标识的论文。但是我不想找到任何关于XV-15的论文。飞机标识是一个ColdFusion变量，我们正在使用MySQL。

目前我正在使用的方法：

WHERE title LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#trim(aircraftDesignation)#%" />

这将返回预期的"The XV-1's Rotor System"，但也会返回我们不想要的"Using RotCFD to Predict Isolated XV-15 Rotor Performance"。

我认为如果我可以查询飞机型号，但不允许在其后紧跟任何数字字符，而允许其他字符如撇号或逗号，那么可能会起作用。然而，我尝试了

'%#trim(aircraftDesignation)#[^0-9]%'

并且

'%#trim(aircraftDesignation)#[!0-9]%'

两者都没有返回任何内容。如果可能的话，我非常感谢任何协助或指导方向！非常感谢您的建议。

更新： 我解释得不好，对此我深表歉意！我们有一个飞机型号字段。相关资源查询仅在访问特定页面时运行。例如，如果访问XV-1页面，则会将飞机型号分配为“XV-1”。相关资源的查询是基于分配的飞机型号（每个页面不同）自动进行的。不采用用户输入。

我不包括“XV-1”后面的数字是为了防止在XV-1页面上显示有关XV-15的论文。XV-15上的论文将显示在XV-15页面上，因为“XV-15”是飞机型号。但是，如果存在XV-167之类的东西，则不会在XV-15页面上显示。

我将其标记为ColdFusion，因为飞机型号是在查询中使用的CF变量。我尝试使用SQL（以及使用REGEXP的研究），但似乎未正确使用变量。或者在REGEXP的情况下，无法将变量与正则表达式结合使用。

希望这有所帮助！非常感谢您的帮助。

另一个更新： 使用REGEXP如下：

WHERE title REGEXP '#trim(aircraftDesignation)#[^0-9]'

目前为止工作正常！标题中带有“XV-1”的论文，以及“XV-1”的论文都显示出来了。但是XV-15的论文没有显示出来。我已经检查了其他搜索结果并似乎都是按预期工作的。

全文搜索确实是一个很好的选择，我一定会进一步研究它。谢谢大家的帮助和建议！我非常感激。

- daltec

---

你或许想尝试使用MySql的REGEX运算符，而非LIKE。http://dev.mysql.com/doc/refman/5.7/en/regexp.html - beloitdavisja

你如何区分XV-1和XV-15？后者在前者之后有一个数字字符，因此在查询条件后不允许数字字符不会产生任何区别。 我认为你需要为飞机指定一个列，并针对该列进行查询。 这不是一个ColdFusion问题。 - Gary Stanton

你的编程工作最好花在搜索页面上。也许相关选择比文本框更好。 - Dan Bracuk

你的数据库中是否包含飞机和文档之间的任何关联？ - Dan Bracuk

并不是说你不能将变量与正则表达式结合使用，而是必须能够将变量内容转换为有效的正则表达式。例如，你可以使用带有可选单词边界的REGEXP。当然，从技术上讲，你可能可以通过LIKE匹配实现相同的结果，但是... SQL代码几乎肯定会更加复杂，可能性能更低。根据你的需求，另一个选择是全文搜索。 - Leigh

Answer 1

我想感谢beloitdavisja在他上面的评论中指引我正确的方向：

“您可能希望尝试使用MySql的REGEX运算符而不是LIKE。dev.mysql.com/doc/refman/5.7/en/regexp.html-beloitdavisja”

这给了我我所要寻找的结果，还有很多其他有用的信息。谢谢！根据Leigh的建议，这是我最终使用的查询（为简洁起见省略了cfqueryparam）：

SELECT sku, title, content, fileName
FROM sd_productsearch
WHERE title REGEXP '#trim(aircraftDesignation)#[^0-9]' AND sku NOT LIKE 'v_%' AND status = '1'
ORDER BY title

一个小解释：此查询仅检索我们的技术论文，而不是我们的一般兴趣杂志文章（SKU号以“v_”开头）。它还仅显示我们图书馆上活动的论文（状态为1）。我使用的REGEXP允许搜索特定飞机的指定飞机类型，但过滤掉后面的任何数字。这类似于我最初尝试做的事情，但我不知道REGEXP运算符。所以再次感谢beloitdavisja！

更新：感谢Leigh提醒变量范围和（尤其是）cfqueryparam。如果有人想知道如何将其与REGEXP一起使用，这对我有效：

WHERE title REGEXP <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(aircraftDesignation)#[^0-9]">

- daltec

---

你最终使用了哪个SQL语句？更新你的答案并包含实际SQL代码片段将有助于下一个遇到这个问题的人。 - Leigh

好观点Leigh！我已经编辑了我的原始问题，但你是对的，我得到的答案应该放在答案中！ :-) 此外，所提到的sd_productsearch表是我们完整产品表的新版本，设置为使用全文搜索。感谢您的建议-我不熟悉全文搜索和单词边界，我们将在其他应用程序中真正地利用它们。非常感谢！ - daltec

很好的跟进。只需记住，REGEXP有保留字符，就像任何语言一样。如果#aircraftDesignation#包含任何这些字符，它可能会破坏SQL或产生意外结果。因此，您可能需要添加一些验证。另外，请不要忘记限定变量范围并使用cfqueryparam（否则查询容易受到SQL注入攻击！）。 - Leigh

没事了...我看到你正在使用cfqueryparam，可能为了简洁省略了它 :) (更新答案以此说明) - Leigh