如何在 MySQL 中搜索多列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2514548/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to search multiple columns in MySQL?
提问by George
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中搜索多个列？
回答by Terra Walker
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%'
回答by akamike
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 查询做什么的更多信息，请尝试文档。
回答by Quassnoi
If your table is MyISAM:
如果您的表是MyISAM：
SELECT *
FROM pages
WHERE MATCH(title, content) AGAINST ('keyword' IN BOOLEAN MODE)
This will be much faster if you create a FULLTEXTindex on your columns:
如果您FULLTEXT在列上创建索引，这会快得多：
CREATE FULLTEXT INDEX fx_pages_title_content ON pages (title, content)
, but will work even without the index.
，但即使没有索引也能工作。
回答by Parth Patel
1)
1)
select *
from employee em
where CONCAT(em.firstname, ' ', em.lastname) like '%parth pa%';
2)
2)
select *
from employee em
where 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
- 帕特尔
- 部分_p
- 帕特尔#parth
回答by Ravi Khatri
Here is a query which you can use to search for anything in from your database as a search result ,
这是一个查询，您可以使用它从数据库中搜索任何内容作为搜索结果，
SELECT * FROM tbl_customer
WHERE CustomerName LIKE '%".$search."%'
OR Address LIKE '%".$search."%'
OR City LIKE '%".$search."%'
OR PostalCode LIKE '%".$search."%'
OR Country LIKE '%".$search."%'
Using this code will help you search in for multiple columns easily
使用此代码将帮助您轻松搜索多列
回答by user3781751
SELECT * FROM persons WHERE (`LastName` LIKE 'r%') OR (`FirstName` LIKE 'a%');
Please try with above query.
请尝试使用上述查询。
