如何在MySQL中搜索多个列？

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?

仅用于搜索一列,我注意到用逗号分隔列名会导致错误。那么有可能在mysql中搜索多个列吗?

5 个解决方案

#1

You can use the AND or OR operators, depending on what you want the search to return.

您可以使用AND或OR运算符,具体取决于您希望搜索返回的内容。

SELECT title FROM pages WHERE my_col LIKE %$param1% AND another_col LIKE %$param2%;

Both clauses have to match for a record to be returned. Alternatively:

两个条款必须匹配要返回的记录。或者:

SELECT title FROM pages WHERE my_col LIKE %$param1% OR another_col LIKE %$param2%;

If either clause matches then the record will be returned.

如果任一子句匹配,则返回记录。

For more about what you can do with MySQL SELECT queries, try the documentation.

有关使用MySQL SELECT查询可以执行的操作的更多信息,请尝试使用文档。

#2

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.

如果它只是用于搜索,那么您可以使用CONCATENATE_WS。这将允许通配符搜索。根据表的大小,可能存在性能问题。

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

#3

If your table is MyISAM:

如果你的桌子是MyISAM:

SELECT  *FROM    pagesWHERE   MATCH(title, content) AGAINST ('keyword' IN BOOLEAN MODE)

This will be much faster if you create a FULLTEXT index on your columns:

如果在列上创建FULLTEXT索引,这将快得多:

CREATE FULLTEXT INDEX fx_pages_title_content ON pages (title, content)

, but will work even without the index.

,但即使没有索引也能工作。

#4

select *from employee emwhere CONCAT(em.firstname, ' ', em.lastname) like '%parth pa%';

select *from employee emwhere CONCAT_ws('-', em.firstname, em.lastname) like '%parth-pa%';

First is usefull when we have data like : 'firstname lastname'.

当我们有以下数据时,首先是有用的:'firstname lastname'。

e.g

parth patel
parth p
patel parth

Second is usefull when we have data like : 'firstname-lastname'. In it you can also use special characters.

当我们有以下数据时,第二个是有用的:'firstname-lastname'。在其中您还可以使用特殊字符。

e.g

parth-patel
parth_p
patel#parth

#5

SELECT * FROM persons WHERE (`LastName` LIKE 'r%') OR (`FirstName` LIKE 'a%');

Please try with above query.

请尝试以上查询。

#1