使用单个关键字在MySQL中使用单个where条件搜索表的所有列 [英] Search all columns of a table using a single where condition with single keyword in mysql

问题描述

我有一个表，其中包含64个不同的字段.我要在其中搜索单个关键字，结果应与任何字段中的关键字匹配.提出一些建议.

I have a table which consists of 64 different fields. i am going to search with a single keyword in it, Results should match the keyword from any field. Give some suggestions.

推荐答案

SELECT * FROM `some_table`
WHERE
CONCAT_WS('|',`column1`,`column2`,`column3`,`column4`,`column64`) # single condition, many columns
LIKE '%VT%'

Voila.

"|"顺便说一句，分隔符是为了防止您发现巧合的匹配，例如，column1以'V'结尾，column2以'T'开头，这会在搜索"VT"时产生误报.

The '|' separator, by the way, is to prevent you finding coincidental matches where, e.g., column1 ends in 'V' and column2 starts with 'T', which would give you a false positive in a search for "VT".

我不确定上述方法是否比OR方法快(我想它们的速度相同)，但是如果您手动编写查询，则肯定会减少键入次数.

I'm not sure if the above method is faster than the OR method (I would guess they're the same speed) , but it definitely involves less typing if you're writing the query by hand.

这篇关于使用单个关键字在MySQL中使用单个where条件搜索表的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！