blocks|key|3869107|text|CASE表达式如何：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3869108|SELECT
++++CASE+WHEN+field1+REGEXP+'[[:<:]]foo[[:>:]]%7C[[:<:]]bar[[:>:]]%7C[[:<:]]foobar[[:>:]]'
+++++++++THEN+'field1'+ELSE+''+END+AS+field1match,
++++CASE+WHEN+field2+REGEXP+'[[:<:]]foo[[:>:]]%7C[[:<:]]bar[[:>:]]%7C[[:<:]]foobar[[:>:]]'
+++++++++THEN+'field2'+ELSE+''+END+AS+field2match,
++++CASE+WHEN+field3+REGEXP+'[[:<:]]foo[[:>:]]%7C[[:<:]]bar[[:>:]]%7C[[:<:]]foobar[[:>:]]'
+++++++++THEN+'field3'+ELSE+''+END+AS+field3match
FROM+yourTable|code-block|syntax|javascript|3869109|​|3869110|Demo|3869111|3869112|如果您只想匹配子字符串，请使用以下命令：|3869113|SELECT
++++CASE+WHEN+field1+REGEXP+'foo%7Cbar%7Cfoobar'
+++++++++THEN+'field1'+ELSE+''+END+AS+field1match,
++++CASE+WHEN+field2+REGEXP+'foo%7Cbar%7Cfoobar'
+++++++++THEN+'field2'+ELSE+''+END+AS+field2match,
++++CASE+WHEN+field3+REGEXP+'foo%7Cbar%7Cfoobar'
+++++++++THEN+'field3'+ELSE+''+END+AS+field3match
FROM+yourTable|3869114|entityMap|0|LINK|mutability|MUTABLE|url|https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=bb6fcbfaa4e088b6947e5d692a320c80^0|0|4|0|0|0|0|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|15|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|16|8|@]|D|@$9|17|A|18|1|19]]|E|$]]|$1|O|3|L|5|6|7|1A|8|@]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1B|8|@]|D|@]|E|$]]|$1|R|3|S|5|H|7|1C|8|@]|D|@]|E|$I|J]]|$1|T|3|-4|5|6|7|1D|8|@]|D|@]|E|$]]]|U|$V|$5|W|X|Y|E|$Z|10]]]]

How about a <code>CASE</code> expression:

<pre><code>SELECT
 CASE WHEN field1 REGEXP '[[:&lt;:]]foo[[:&gt;:]]|[[:&lt;:]]bar[[:&gt;:]]|[[:&lt;:]]foobar[[:&gt;:]]'
 THEN 'field1' ELSE '' END AS field1match,
 CASE WHEN field2 REGEXP '[[:&lt;:]]foo[[:&gt;:]]|[[:&lt;:]]bar[[:&gt;:]]|[[:&lt;:]]foobar[[:&gt;:]]'
 THEN 'field2' ELSE '' END AS field2match,
 CASE WHEN field3 REGEXP '[[:&lt;:]]foo[[:&gt;:]]|[[:&lt;:]]bar[[:&gt;:]]|[[:&lt;:]]foobar[[:&gt;:]]'
 THEN 'field3' ELSE '' END AS field3match
FROM yourTable
</code></pre>

<a href="https://dbfiddle.uk/?rdbms=mariadb_10.3&amp;fiddle=bb6fcbfaa4e088b6947e5d692a320c80" rel="nofollow noreferrer"><h2>Demo</h2></a>

If you just want to match substrings, then use this:

<pre><code>SELECT
 CASE WHEN field1 REGEXP 'foo|bar|foobar'
 THEN 'field1' ELSE '' END AS field1match,
 CASE WHEN field2 REGEXP 'foo|bar|foobar'
 THEN 'field2' ELSE '' END AS field2match,
 CASE WHEN field3 REGEXP 'foo|bar|foobar'
 THEN 'field3' ELSE '' END AS field3match
FROM yourTable
</code></pre>

blocks|key|3869137|text|可以使用条件将标志添加到select中，例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3869138|SELECT+*,
++++IF(field1+LIKE+'%25foo%25',+1,+0)+AS+field1_matches,
++++IF(field2+LIKE+'%25bar%25',+1,+0)+AS+field2_matches,
++++IF(field3+LIKE+'%25foobar%25',+1,+0)+AS+field3_matches
FROM+TABLE
WHERE+field1+LIKE+'%25foo%25'
++++OR+field2+LIKE+'%25bar%25'
++++OR+field3+LIKE+'%25foobar%25'|code-block|syntax|javascript|3869139|如果需要将单个字段中的结果作为逗号分隔列表，则可以使用CONCAT_WS连接结果，并在没有匹配时在条件内返回NULL：|offset|length|style|CODE|3869140|SELECT+*,
++++CONCAT_WS(',',
++++++++IF(field1+LIKE+'%25foo%25',+'field1',+NULL),
++++++++IF(field2+LIKE+'%25bar%25',+'field2',+NULL),
++++++++IF(field3+LIKE+'%25foobar%25',+'field3',+NULL)
++++)+AS+matches
FROM+TABLE
WHERE+field1+LIKE+'%25foo%25'
++++OR+field2+LIKE+'%25bar%25'
++++OR+field3+LIKE+'%25foobar%25'|3869141|entityMap^0|0|0|R|9|1I|4|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

You can add flags using conditionals to your select like:

<pre><code>SELECT *,
 IF(field1 LIKE '%foo%', 1, 0) AS field1_matches,
 IF(field2 LIKE '%bar%', 1, 0) AS field2_matches,
 IF(field3 LIKE '%foobar%', 1, 0) AS field3_matches
FROM TABLE
WHERE field1 LIKE '%foo%'
 OR field2 LIKE '%bar%'
 OR field3 LIKE '%foobar%'
</code></pre>

If you need the results in a single field as a comma separated list you can concatenate the results using <code>CONCAT_WS</code> and return <code>NULL</code> inside your conditional when there's no match:

<pre><code>SELECT *,
 CONCAT_WS(',',
 IF(field1 LIKE '%foo%', 'field1', NULL),
 IF(field2 LIKE '%bar%', 'field2', NULL),
 IF(field3 LIKE '%foobar%', 'field3', NULL)
 ) AS matches
FROM TABLE
WHERE field1 LIKE '%foo%'
 OR field2 LIKE '%bar%'
 OR field3 LIKE '%foobar%'
</code></pre>

blocks|key|1944560|text|使用+CONCAT和CASE|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|CODE|entityRanges|data|1944561|您正在寻找以下结果，但我知道您希望用逗号或任何其他符号分隔字段，因为我现在无法访问mysql，所以我认为您可以自己处理|1944562|SELECT+*,
++++CONCAT(CASE+WHEN+field1+LIKE+'%25foo%25'+THEN+'field1'+ELSE+NULL+END,
++++++++++++CASE+WHEN+field2+LIKE+'%25bar%25'+THEN+'field2'+ELSE+NULL+END,
++++++++++++CASE+WHEN+field3+LIKE+'%25foobar%25'+THEN+'field3'+ELSE+NULL+END)+AS+macthed_column
FROM+youtable
WHERE+field1+LIKE+'%25foo%25'
++++OR+field2+LIKE+'%25bar%25'
++++OR+field3+LIKE+'%25foobar%25'|code-block|syntax|javascript|1944563|输出|1944564|ID++field1++field2++field3++++++macthed_column
1+++foo+++++xxx+++++xxx+++++++++field1
2+++xxx+++++12bar21+xxfoobarxx++field2field3
3+++f2o+++++bar+++++yxz+++++++++field2|1944565|entityMap^0|0|2|3|6|A|4|0|15|5|0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|D]|$9|Y|A|Z|B|D]]|E|@]|F|$]]|$1|G|3|H|5|6|7|10|8|@$9|11|A|12|B|D]]|E|@]|F|$]]|$1|I|3|J|5|K|7|13|8|@]|E|@]|F|$L|M]]|$1|N|3|O|5|6|7|14|8|@$9|15|A|16|B|C]]|E|@]|F|$]]|$1|P|3|Q|5|K|7|17|8|@]|E|@]|F|$L|M]]|$1|R|3|-4|5|6|7|18|8|@]|E|@]|F|$]]]|S|$]]

Using <code>CONCAT</code> and <code>CASE</code>

You are exactly looking for the below result but I know you wish to separate fields with comma or any other sign, for this I can't access <code>mysql</code> now so I think you can manage that by yourself 

<pre><code>SELECT *,
 CONCAT(CASE WHEN field1 LIKE '%foo%' THEN 'field1' ELSE NULL END,
 CASE WHEN field2 LIKE '%bar%' THEN 'field2' ELSE NULL END,
 CASE WHEN field3 LIKE '%foobar%' THEN 'field3' ELSE NULL END) AS macthed_column
FROM youtable
WHERE field1 LIKE '%foo%'
 OR field2 LIKE '%bar%'
 OR field3 LIKE '%foobar%'
</code></pre>

OUTPUT:

<pre><code>ID field1 field2 field3 macthed_column
1 foo xxx xxx field1
2 xxx 12bar21 xxfoobarxx field2field3
3 f2o bar yxz field2
</code></pre>

given a mysql query in the form like this: 

<pre><code>SELECT *
FROM table
WHERE field1 LIKE '%foo%'
 OR field2 LIKE '%bar%'
 OR field3 LIKE '%foobar%'
</code></pre>

Which is the best way to tag the "matching fields" ? 

For example:

<pre class="lang-none prettyprint-override"><code>+----+--------+--------+----------+
| ID | field1 | field2 | field3 |
+----+--------+--------+----------+
| 01 | foo | xxx | xxx |
+----+--------+--------+----------+
| 02 | xxx |12bar21 |xxfoobarxx|
+----+--------+--------+----------+
| 03 | f2o | bar | yxz |
+----+--------+--------+----------+
</code></pre>

Here every record would be matched - now I want to know which field for each record matches. (So for ID 01 it is field1, for ID 02 it is field2, field3 and for ID 03 it is field2)

Thank you

Mysql query, multiple where conditions, which field matches?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

给出如下形式的mysql查询：SELECT *FROM tableWHERE field1 LIKE '%foo%'    OR field2 LIKE '%bar%'    OR field3 LIKE '%foobar%'哪种方式是标记“匹配字段”的最佳方式？例如：+----+--------+--------+-...

问Mysql查询，多个哪里的条件，哪个字段匹配？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Mysql查询，多个哪里的条件，哪个字段匹配？EN