在MATCH()AGAINST()中查找匹配列的名称或别名 [英] Find name or alias of matched column in MATCH() AGAINST()

问题描述

我正在使用MATCH() AGAINST()在数据库中进行全文搜索.由于我要从多个表中获取数据，因此我创建了VIEW并在MARIADB，

I am using MATCH() AGAINST() for full text search in my database. As since i wanted data from multiple tables, i created a VIEW and am using following format of query in MARIADB,

SELECT 
DAT1, DAT2, DAT3 
FROM VIEW1
WHERE 
MATCH (COL1) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) OR
MATCH (COL2) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) OR
MATCH (COL3) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE)

一切正常，但是我正在查询中寻找附加输出，这就是我在其中找到搜索结果的列名.

Everything is working fine, but however i am looking for an addition output in query, and that is the Column Name i found search result in.

如何在查询的输出中获取匹配字符串的COL1，COL2和COL3的名称或别名?

How can i fetch the name or alias of COL1, COL2 and COL3 of matched string in the output of query?

推荐答案

您可以在select中使用case表达式:

You can use a case expression in the select:

SELECT DAT1, DAT2, DAT3,
       CONCAT_WS(',',
                 (CASE WHEN MATCH(COL1) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 THEN 'col1' END),
                 (CASE WHEN MATCH(COL2) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 THEN 'col2' END),
                 (CASE WHEN MATCH(COL2) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 THEN 'col3' END)
                ) as cols
FROM VIEW1
WHERE MATCH (COL1) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 OR
      MATCH (COL2) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 OR
      MATCH (COL3) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0;

这篇关于在MATCH()AGAINST()中查找匹配列的名称或别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！