在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%';

有什么建议吗?

编辑:我正在测试的名称是拉里·史密斯".数据库在"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.

今天上午，我尝试了罗伯特·甘布尔的答案.他和我昨晚的跑步非常相似.我无法解释，但是今天早上它起作用了.我能想到的唯一区别是，昨晚我在搜索查询的第三个或"段中运行了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屋！