返回使用PHP找到搜索项的列名(s)。

Example: I have a table with 5 fields, named id, field_1, field_2, field_3, field_4 And I am searching for 'foo' across all fields.

示例:我有一个包含5个字段的表，命名为id、field_1、field_2、field_3、field_4，我正在所有字段中搜索“foo”。

SELECT ID FROM table WHERE field_1 LIKE ('%foo%') OR field_2 LIKE ('%foo%') OR ...

I'd like to return the IDs, as well as which fields the term was found in.

我想返回id，以及该术语在哪个字段中被找到。

What would be the most efficient way to do this?

最有效的方法是什么?

Note: I am looking for a solution that could dynamically accommodate adding new DB fields, without having to manually update the SQL.

注意:我正在寻找一个可以动态适应添加新DB字段的解决方案，而不必手动更新SQL。

2 个解决方案

#1

One possible approach is to map these matches in returned columns:

一种可能的方法是在返回的列中映射这些匹配:

SELECT ID, 
       field_1 LIKE '%foo%' AS field_1_match,
       field_2 LIKE '%foo%' AS field_2_match
...

... so you can just check each corresponding column_match value to know, well, was it matched or not.

…所以你可以检查每个对应的column_match值来知道它是否匹配。

It's easy to extend this into returning a string with columns (separated by ,, for example) with CONCAT_WS:

可以很容易地将其扩展为返回一个带有CONCAT_WS的列(例如，以)为字符串的字符串:

SELECT ID, 
       CONCAT_WS(',',
         IF(field_1 LIKE '%foo%', 'field_1', NULL),
         IF(field_2 LIKE '%foo%', 'field_2', NULL)
         ... 
       )

... but, honestly speaking, I doubt it'll be easier to process data formatted this way.

…但是，老实说，我怀疑用这种方式处理数据会更容易些。

#2

Use unions:

使用工会:

SELECT id, 'field_1' as 'fieldName' FROM table WHERE field1 LIKE '%foo%' UNION
SELECT id, 'field_2' as 'fieldName' FROM table WHERE field2 LIKE '%foo%' UNION
...

This will return the id and column name wherever it is found. If it happens multiple times on the same row, multiple results will be returned for that row.

这将返回id和列名。如果它在同一行中多次发生，那么将为该行返回多个结果。

Updated:

更新:

It is possible to dynamically search all of the tables using a stored procedure and a cursor.

可以使用存储过程和游标动态地搜索所有表。

DELIMITER //

CREATE PROCEDURE search_all_fields(IN search VARCHAR(100), IN tableName VARCHAR(100), IN idColumnName VARCHAR(100)) 
BEGIN

    DECLARE sqlQuery VARCHAR(200);
    DECLARE done INT DEFAULT 0;
    DECLARE columnName VARCHAR(30);
    DECLARE cur CURSOR FOR SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME`= tableName; # cursor will iterate over the column names
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    CREATE TEMPORARY TABLE temp ( id VARCHAR(100), FieldName VARCHAR(100) ); # procedure returns multiple result sets, so we'll dump them in a temp table and get them at the end
    OPEN cur;

    read_loop: LOOP # iterates through column names
        FETCH cur INTO columnName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        # execute search
        SET @sqlQuery = CONCAT("INSERT INTO temp SELECT ", idColumnName, ", '", columnName, "' as 'FieldName' FROM ", tableName, " WHERE ", columnName, " LIKE '%", search, "%'");
        PREPARE stmt FROM @sqlQuery;
        EXECUTE stmt;
    END LOOP;

    CLOSE cur;
    # grab the results
    SELECT * FROM temp;
END;//

DELIMITER ;

#1