在mysql中使用单个关键字的单个where条件搜索表的所有列

2021-11-20 00:00:00 sql search 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%'

瞧.

'|'顺便说一下,分隔符是为了防止您找到巧合的匹配,例如,第 1 列以V"结尾,第 2 列以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.

相关文章