我正在尝试制作类似搜索页面的功能.我想查询"ColumnA"和"ColumnB"以及"ColumnC"和"ColumnD"中的"查询".并在任何列中选择包含单词/短语"query"的行.这似乎有效:
SELECT * FROM projects WHERE category LIKE '%query%' OR name LIKE '%query%' OR description LIKE '%query%'OR keywords LIKE '%query%' OR 'type' LIKE '%query%' ORDER BY name ASC
但它很冗长.有没有更容易或更有效的方法来做到这一点?
简单的解决方法:
SELECT * FROM projects WHERE CONCAT(category,name,description,keywords,type) LIKE '%query%' ORDER BY name ASC;
如果需要,您可以在列之间添加分隔符:
SELECT * FROM projects WHERE CONCAT(category,"|",name,"|",description,"|",keywords,"|",type) LIKE '%query%' ORDER BY name ASC;
您还可以使用全文搜索(您需要创建全文索引,如下所述:多列上的FULLTEXT INDEXES如何工作?)
SELECT *, MATCH (category,name,description,keywords,type) AGAINST ('query') AS score FROM projects WHERE MATCH (category,name,description,keywords,type) AGAINST ('query');