mysql - 使用全文搜索无法返回精确匹配？

Question

我有两张 table ，分别是歌曲和歌词。现在我使用全文搜索让人们搜索他们想要的歌曲或歌词。但是第一个结果中没有出现完全匹配的内容。

示例:我想搜索歌曲或歌词名称“Love and Let Love”。但它返回如下:

        [0] => Array
            (
                [song_title] => This Is Love
                [type] => songs
                [point] => 3.9282567501068115
            )

        [1] => Array
            (
                [song_title] => I Will Love Again 2014
                [type] => songs
                [point] => 3.8840975761413574
            )

        [2] => Array
            (
                [song_title] => A Love I Think Will Last
                [type] => lyric
                [point] => 3.811438798904419
            )

        [3] => Array
            (
                [song_title] => Love and Let Love
                [type] => lyric
                [point] => 3.811438798904419
            )

这是我的代码:

public function searchSongs2($keyword, $start, $limit,$deleted=0)
{
    $sql = " (SELECT s.song_title, 'songs' as type ,MATCH(s.song_title) AGAINST ('".$keyword."' IN NATURAL LANGUAGE MODE) as point
              FROM song s
              JOIN category_song cs ON cs.song_id = s.song_id
              JOIN category c ON c.category_id = cs.category_id
              WHERE s.song_type ='publish' AND MATCH(s.song_title) AGAINST ('".$keyword."' IN NATURAL LANGUAGE MODE) 
              GROUP BY s.song_title  )  
       UNION
              (SELECT l.lyric_name, 'lyric' as type  ,MATCH(l.lyric_name) AGAINST ('".$keyword."' IN NATURAL LANGUAGE MODE) as point
              FROM ml_lyric l
              JOIN ml_singer s ON s.singer_id = l.singer_id
              JOIN ych_category_lyric cl ON cl.lyric_id = l.lyric_id
              JOIN ych_category c ON c.category_id = cl.category_id
              WHERE MATCH(l.lyric_name) AGAINST ('".$keyword."' IN NATURAL LANGUAGE MODE)
          GROUP BY l.lyric_name  ) 
  ORDER BY point DESC  LIMIT ".$start.",".$limit;
    return Yii::app()->db->createCommand($sql)->queryAll();
}

有没有办法让人们搜索的字符串始终排在第一位？

Answer 1

已编辑，使其实际有效:

可能不是最优雅的，但刚刚经过测试，它可以工作。我确信其他人可以把它美化一下。

$myarray = array(
         array
            (
                'song_title' => 'This Is Love',
                'type' => 'songs',
                'point' => '3.9282567501068115'
            ),

        array
            (
                'song_title' => 'I Will Love Again 2014',
                'type' => 'songs',
                'point' => '3.8840975761413574'
            ),

        array
            (
                'song_title' => 'A Love I Think Will Last',
                'type' => 'lyric',
                'point' => '3.811438798904419'
            ),

        array
            (
                'song_title' => 'Love and Let Love',
                'type' => 'lyric',
                'point' => '3.811438798904419'
            ),
     );
$new_array = array();
foreach ( $myarray as $key => $value ) {
   if ( $value['song_title'] == 'Love and Let Love' ) {
        $new_array[0]['song_title'] = $value['song_title'];
        $new_array[0]['type'] = $value['type'];
        $new_array[0]['point'] = $value['point'];
                unset($myarray[$key]);
    }
}
     $new_array = array_merge($new_array, $myarray);
    print_r ( $new_array );

返回:

Array
(
    [0] => Array
        (
            [song_title] => Love and Let Love
            [type] => lyric
            [point] => 3.811438798904419
        )

    [1] => Array
        (
            [song_title] => This Is Love
            [type] => songs
            [point] => 3.9282567501068115
        )

    [2] => Array
        (
            [song_title] => I Will Love Again 2014
            [type] => songs
            [point] => 3.8840975761413574
        )

    [3] => Array
        (
            [song_title] => A Love I Think Will Last
            [type] => lyric
            [point] => 3.811438798904419
        )

)