我必须在查询中更改哪些内容才能首先获得精确匹配?
例如,任何人在搜索表单中输入快乐歌曲,现在他会得到这个输出..
快乐的孩子
快乐的日子
快乐的人
欢乐歌
快乐站
...
这是我的查询..
SELECT `artist`, `song`, `genre`, `pass`, `id`
FROM `songs`
JOIN `artists` USING (`pass`)
WHERE MATCH(`artist`) AGAINST("happy song")
OR MATCH(`song`, `genre`) AGAINST("happy song")
ORDER BY `song` ASC
LIMIT 0,30
') or die(mysql_error($_connect));```
Thank you!
EDIT 03.07.22
Changed query to:<br>
```$query = mysqli_query($_connect, '
SELECT `artist`, `song`, `genre`, `pass`, `id`
FROM `songs`
JOIN `artists` USING (`pass`)
WHERE MATCH(`artist`) AGAINST("happy song")
OR MATCH(`song`, `genre`) AGAINST("happy song")
ORDER BY `song` = "happy song" DESC
') or die(mysql_error($_connect));```
The output is the same, result with only "song" in the text are above in the result when searched for "happy song"
**EDIT 14.7.22**
**Table Artists:**
CREATE TABLE `artists` (
`artist` varchar(40) NOT NULL,
`pass` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PACK_KEYS=1;
ALTER TABLE `artists`
ADD PRIMARY KEY (`pass`),
ADD UNIQUE KEY `uni_que` (`artist`) USING BTREE;
ALTER TABLE `artists` ADD FULLTEXT KEY `full_txt` (`artist`);
COMMIT;
**Table Songs:**
CREATE TABLE `songs` (
`song` varchar(80) NOT NULL,
`tags` varchar(40) NOT NULL,
`style` varchar(20) NOT NULL,
`pass` bigint(20) UNSIGNED NOT NULL,
`id` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PACK_KEYS=1;
ALTER TABLE `songs`
ADD PRIMARY KEY (`id`);
ALTER TABLE `songs` ADD FULLTEXT KEY `song` (`song`,`tags`,`style`);
COMMIT; Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
您当前正在要求使用
ORDER BY 歌曲 ASC按字母顺序排序更改为
ORDER BY MATCH(...) AGAINST(...) DESC这可能会将“一首关于快乐孩子的歌曲”放在第一位。也就是说,如果你首先需要“欢歌”,我们就需要更加努力:
SELECT ... FROM ... WHERE ... ORDER BY song = "happy song" DESC, MATCH(...) AGAINST (...)(由于您有
OR并涉及多个列,因此查询可能会变得相当混乱。)如果您不想要只有一个单词的歌曲,请参阅
+运算符,或者MATCH(song) AGAINST('+"happy song"')注意引号的有意使用。