问题描述
这是我的查询:
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!