获取匹配列 &它的价值 MySQL [英] Get matching column & its value MySQL

问题描述

这是我的查询:

SELECT ID, Name, Description
FROM MyTable
WHERE NAME LIKE :search OR Description LIKE '%search%' 
      OR Status LIKE '%search%'
      OR ... Many Other Columns LIKE '%search%

当我获得数据时，我会将其显示给他们搜索的用户.现在我只显示 ID、Name &说明.我还想显示与搜索字符串匹配的列.如果它匹配 Status 我想显示行的状态，如果它匹配任何其他列我想显示它匹配的那列的值.

When I get the data I display it to the user that they searched for. Now I only display ID, Name & Description. I also want to display which column matched the searching string. If it matched Status I want to display the status of the row, if it matched any other column I want to display the value of that column that it matched.

是否匹配多列无关紧要.我只需要第一个.

这是否可能不选择所有列&然后在每个上运行 PHP 搜索以找出哪个匹配?可以简单地在MySQL中完成吗?

Is this possible without selecting all columns & then running PHP search on each to find out which one matched? Can it be done simply in MySQL?

我想尽可能快，因为我的搜索变得越来越慢我添加到搜索中的更多表，不希望此功能增加大量开销.

I want to make it as fast as possible since my search is becoming pretty slow the more columns & more tables I add to the search and do not want this feature to add a lot of overhead.

推荐答案

考虑以下...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL);

INSERT INTO my_table (a,b,c) VALUES 
(101,102,103),
(102,103,104),
(103,104,105),
(104,105,106),
(105,106,107);

 SELECT * FROM my_table;
+----+-----+-----+-----+
| id | a   | b   | c   |
+----+-----+-----+-----+
|  1 | 101 | 102 | 103 |
|  2 | 102 | 103 | 104 |
|  3 | 103 | 104 | 105 |
|  4 | 104 | 105 | 106 |
|  5 | 105 | 106 | 107 |
+----+-----+-----+-----+

SELECT id
     , CASE WHEN a LIKE '%04%' THEN 'a' WHEN b LIKE '%04%' THEN 'b' WHEN c LIKE '%04%' THEN 'c' END col 
     , CASE WHEN a LIKE '%04%' THEN a WHEN b LIKE '%04%' THEN b WHEN c LIKE '%04%' THEN c END val 
  FROM my_table;
+----+------+------+
| id | col  | val  |
+----+------+------+
|  1 | NULL | NULL |
|  2 | c    |  104 |
|  3 | b    |  104 |
|  4 | a    |  104 |
|  5 | NULL | NULL |
+----+------+------+

这篇关于获取匹配列 &它的价值 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！