在 WHERE 子句中使用 mysql concat()? [英] Using mysql concat() in WHERE clause?
问题描述
我想在我的表中搜索一列名字和一列姓氏.我目前接受一个字段中的搜索词并将其与两列进行比较,一次一个与
I would like to search my table having a column of first names and a column of last names. I currently accept a search term from a field and compare it against both columns, one at a time with
select * from table where first_name like '%$search_term%' or
last_name like '%$search_term%';
这适用于单字搜索词,但结果集包括所有名字为Larry"的人.但是,如果有人输入名字,然后是空格,然后是姓氏,我想要更窄的搜索结果.我尝试了以下但没有成功.
This works fine with single word search terms but the result set includes everyone with the name "Larry". But if someone enters a first name then a space, then a last name, I want a narrower search result. I've tried the following without success.
select * from table where first_name like '%$search_term%' or last_name
like '%$search_term%' or concat_ws(' ',first_name,last_name)
like '%$search_term%';
有什么建议吗?
我正在测试的名字是Larry Smith".数据库在first_name"列中存储Larry",在last_name"列中存储Smith".数据干净,没有多余的空格,搜索词被左右修剪.
The name I'm testing with is "Larry Smith". The db stores "Larry" in the "first_name" column, and "Smith" in the "last_name" column. The data is clean, no extra spaces and the search term is trimmed left and right.
编辑 2: 今天早上我尝试了 Robert Gamble 的答案.他和我昨晚跑步的很相似.我无法解释,但今天早上它有效.我能想到的唯一区别是昨晚我将 concat 函数作为搜索查询的第三个或"段(在查看 first_name 和 last_name 之后)运行.今天早上,在浏览了上述内容以及地址和公司名称后,我将它作为最后一部分运行.
EDIT 2: I tried Robert Gamble's answer out this morning. His is very similar to what I was running last night. I can't explain it, but this morning it works. The only difference I can think of is that last night I ran the concat function as the third "or" segment of my search query (after looking through first_name and last_name). This morning I ran it as the last segment after looking through the above as well as addresses and business names.
在查询结束时运行 mysql 函数是否比在中间运行更好?
Does running a mysql function at the end of a query work better than in the middle?
推荐答案
你所拥有的应该有效,但可以简化为:
What you have should work but can be reduced to:
select * from table where concat_ws(' ',first_name,last_name)
like '%$search_term%';
您能否提供一个不起作用的示例名称和搜索词?
Can you provide an example name and search term where this doesn't work?
这篇关于在 WHERE 子句中使用 mysql concat()?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!