获取匹配列及其值MySQL

Question

Here is my query:

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

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.

Does not matter if it matches multiple columns. I only need the first one.

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.

Answer 1

Consider the following...

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 |
+----+------+------+