How to search multiple columns in MySQL?

sql mysql full-text-search
George picture George · Mar 25, 2010 · Viewed 110.1k times · Source

I'm trying to make a search feature that will search multiple columns to find a keyword based match. This query:

SELECT title FROM pages LIKE %$query%;

works only for searching one column, I noticed separating column names with commas results in an error. So is it possible to search multiple columns in mysql?

Answer

Terra Walker picture Terra Walker · May 21, 2015

If it is just for searching then you may be able to use CONCATENATE_WS. This would allow wild card searching. There may be performance issues depending on the size of the table.

SELECT * 
FROM pages 
WHERE CONCAT_WS('', column1, column2, column3) LIKE '%keyword%'

Related questions

Fulltext Search with InnoDB

I'm developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly. I was originally planning on using MyISAM for the tables (for the built-in fulltext …

Read More
Search for "whole word match" in MySQL

I would like to write an SQL query that searches for a keyword in a text field, but only if it is a "whole word match" (e.g. when I search for "rid", it should not match "arid", but it …

Read More
mysql query - contains?

how can i search for a particular string in mysql? I tried using contains- gives me error: SELECT r.name , r.network , r.namestring , i.name , r.rid , i.id , d.dtime , d.ifInOctets , d.description FROM router AS r …

Read More