本文是小编为大家收集整理的关于摆脱WHERE子句中的函数的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
i有一个名为users的表，其中包含列firstname和lastname.
我正在努力构建如何在匹配的名字，姓，名字和空间和姓氏以及姓氏以及逗号和逗号和名字的匹配的返回结果上返回结果.例如，" John"，" Doe"，" John Doe"和" Doe，John".它也应该适用于部分补丁(即" John D").我正在考虑类似以下内容(用搜索短语替代).
SELECT * FROM people WHERE
firstname LIKE ?
OR CONCAT_WS(" ",firstname,lastname) LIKE ?
OR lastname LIKE ?
OR CONCAT_WS(", ",lastname,firstname) LIKE ?
这种方法存在一些缺陷两者都给出时的名字和姓氏匹配.
这种方法也存在一个很大的缺陷，因为我在其中的条款中具有函数，这阻止了索引使用并大大降低了性能.
我可以仅使用SQL有效地执行此操作，而无需在Where子句中使用函数，或者我应该用户服务器代码(IE PHP)来解析空格和逗号的给定搜索短语并根据结果创建动态SQL查询?
推荐答案
您问您是否可以有效地在MySQL中执行此操作，鉴于您的模式设计而无需使用 fullText 搜索简单的答案是否，您不能有效地做到这一点.
您可以使用喜欢/strcmppmp 和/或字符串函数.如果要采用这种方法，最好编写一些应用程序逻辑来创建查询内联，而不是尝试编写一个可以处理所有内容的查询.无论哪种方式，都不可能是真正有效的
mySQL 5.6具有 fulltext Innodb中的搜索.如果您使用的版本较低，则只能使用Myisam表进行此操作 - Myisam可能不是一个好主意的原因很多.
另一种方法是查看一个真实的搜索解决方案，例如 sphinx 或其他推荐答案 您是否尝试过正则表达式 http:http://dev. mysql.com/doc/refman/5.1/en/regexp.html
问题描述
I have a table called users which contains the columns firstname and lastname.
I am struggling on how to structure the WHERE clause to return results on matched first name, last name, first name plus a space and the last name, and last name plus a comma and the first name. For instance, "John", "Doe", "John Doe", and "Doe, John". It should also work for partial patches (i.e. "John D"). I am thinking of something like the following (substitute ? with the search phrase).
SELECT * FROM people WHERE
firstname LIKE ?
OR CONCAT_WS(" ",firstname,lastname) LIKE ?
OR lastname LIKE ?
OR CONCAT_WS(", ",lastname,firstname) LIKE ?
There is a little flaw with this approach as searching on "John Doe" will also return "John Enders", "John Flaggens", and "John Goodmen", so I will need to add some conditional to just return results when both first and last name match when both are given.
There is also a big flaw with this approach as I have functions in my WHERE clause which prevent the use of indexes and result in significantly reduce performance.
Can I effectively do this using just SQL without using functions in my WHERE clause, or should I user server code (ie PHP) to parse the given search phrase for spaces and commas and create a dynamic SQL query based on the results?
推荐答案
You ask if you can efficiently do this within MySQL, given your schema design without using FULLTEXT search the simple answer is no, you can't do this efficiently.
You could create some wacky query using LIKE / STRCMP and / or string functions. If you were to take this approach it will be much better to write some application logic to create the query inline rather than trying to write one query that can handle everything. Either way it’s not likely be truly efficient
MySQL 5.6 has the ability to perform FULLTEXT searches within INNODB. If you’re using a lower version you can only do this with MyISAM tables – there are many reasons why MyISAM may not be a good idea.
Another approach is to look at a real search solution such as Lucene, Sphinx or Xapian
其他推荐答案
Have you tried regular expressions http://dev.mysql.com/doc/refman/5.1/en/regexp.html
更多相关问答
联系站长