在mysql中使用单个关键字的单个where条件搜索表的所有列
我有一个包含 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.
相关文章