MySQL - Is it possible to use LIKE on all columns

问题:

I'm trying to make a simple search bar that searches through my database for certain words. It is possible to use the LIKE attribute without using WHERE? I want it to search all columns for the keywords, not just one. Currently I have this:

mysql_query("SELECT * FROM shoutbox WHERE name LIKE '%$search%' ")

Which obviously only searches for names with the search input. I tried both of these:

mysql_query("SELECT * FROM shoutbox LIKE '%$search%' ")
mysql_query("SELECT * FROM shoutbox WHERE * LIKE '%$search%' ")

and neither worked. Is this something that is possible or is there another way to go about it?

回答1:

You might want to look at the MATCH() function as well eg:

SELECT * FROM shoutbox 
WHERE MATCH(`name`, `foo`, `bar`) AGAINST ('$search')

You can also add boolean mode to this:

SELECT * FROM shoutbox 
WHERE MATCH(`name`, `foo`, `bar`) AGAINST ('$search') IN BOOLEAN MODE

You can also get the relevance scores and add FULLTEXT keys to speed up the queries.

回答2:

There's no shortcut. You need to specify each column separately.

SELECT * FROM shoutbox 
    WHERE name LIKE '%$search%' 
        OR foo LIKE '%$search%' 
        OR bar LIKE '%$search%'  
        OR baz LIKE '%$search%' 

回答3:

There IS a shortcut ! ;)

SELECT * FROM shoutbox 
WHERE CONCAT(name, foo, bar, baz) LIKE '%$search%' 

回答4:

this will not show duplicate rows anymore.

SELECT * FROM shoutbox 
WHERE (name LIKE '%$search%' 
    OR foo LIKE '%$search%' 
    OR bar LIKE '%$search%'  
    OR baz LIKE '%$search%')