php - mysql - 如果表中有任何匹配项，则选择表的第一行

Question

如果表 X 的任意行中有匹配项，我如何返回表 X 的第一行？在 foreach 循环中，我有以下查询。

$sql_string="SELECT id,keyword_tags,topic_name,contents,
            MATCH(keyword_tags) AGAINST ('$keysearch' IN BOOLEAN MODE) AS score1,
            MATCH(topic_name) AGAINST ('$keysearch' IN BOOLEAN MODE) AS score2,
            MATCH(contents) AGAINST ('$keysearch' IN BOOLEAN MODE) AS score3
            FROM ".$table_get." WHERE
            (MATCH(keyword_tags) AGAINST ('$keysearch' IN BOOLEAN MODE) OR
            MATCH(topic_name) AGAINST ('$keysearch' IN BOOLEAN MODE) OR
            MATCH(contents) AGAINST ('$keysearch' IN BOOLEAN MODE))
            AND id='1'
            ORDER BY score1 DESC, score2 DESC, score3 DESC";

如果表中有任何匹配项，关于如何返回 id='1' 的行有什么想法吗？

Answer 1

How can I return the first row of table X if there is a match in ANY ROW of table X?

SQL 标准 EXISTS(子查询) 构造可以帮助:

SELECT ...
  FROM X
 WHERE id = 1                -- our "first" row only
       AND
       EXISTS (SELECT *      -- only if there EXISTS a MATCH anywhere in table
                 FROM X
                WHERE MATCH(field1) AGAINST ('search_val' IN BOOLEAN MODE)
                      OR
                      MATCH(field2) AGAINST ('search_val' IN BOOLEAN MODE)
                      ...
                      MATCH(fieldN) AGAINST ('search_val' IN BOOLEAN MODE));