在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%'

瞧.

'|'顺便说一下，分隔符是为了防止您找到巧合的匹配，例如，第 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.

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