MySQL LIKE运算符&通配符 [英] MySQL LIKE operators & wildcards

问题描述

我一直在阅读教程，但没有学到任何新知识.我有一张顾客桌子.客户的名字和姓氏存储在单独的列中.我想编写一个查询，该查询可以按名字(名字，姓氏或名字)搜索客户.

I've been reading tutorials and have learned nothing new. I have a table of customers. The customer's first and last names are stored in separate columns. I want to write a query that can search for customers by name, either first, last or BOTH.

这就是我所拥有的:

$queryyy = "
    SELECT *
    FROM `customers`
    WHERE
        `first_name1` LIKE '".mysql_real_escape_string($_GET['custname'])."%'
        OR `last_name1` LIKE '%".mysql_real_escape_string($_GET['custname'])."'
        AND `status` = 'active'
    LIMIT 6
"; 

如果我想找到"Lindsay Thompson"，我可以查询"lindsay"或"Thompson"并得到想要的结果，但是如果我查询"lindsay汤普森"，我什么也没得到.

If I want to find "Lindsay Thompson", I can query for "lindsay", or for "Thompson" and get the results I want, but if I query for "lindsay thompson" I get nothing.

我觉得我错过了通配符的要点，或者没有正确使用通配符.有人可以向我解释一下并更正我的查询吗..

I feel like I'm missing the point of the wildcards, or not using them properly. Can someone please explain this to me and correct my query..

谢谢

推荐答案

引入通配符以表示任意数量的任何字符"(在%的情况下).

Wildcards are introduced to express "any number of any characters" (in case of %).

所以

col LIKE '%foo'

将匹配foo值和barfoo值.

您想要的实际上是相反的-您需要连接两列并检查其是否等于请求，例如:

What you want is actually the opposite - you need to concatenate two columns and check if it's equal to the request, like:

CONCAT(first_name, ' ', last_name) = 'foo bar'

这篇关于MySQL LIKE运算符&通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！