我使用布爾全文搜索從數據庫列中搜索單詞。我想獲得與我的記錄完全匹配的記錄，還包括通配符搜索。我得到了正確的結果，但我想首先通過完全匹配來設置該結果的順序。有沒有可能的方法來實現這一目標？我還想對每個單詞進行搜索并按相關性獲取記錄。我試過使用以下查詢，但它不起作用，因為它給了我錯誤的記錄排名。 SELECT id, search_tags, MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ") IN BOOLEAN MODE) AS relevance_rank FROM images WHERE MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ") IN BOOLEAN MODE) ORDER BY relevance_rank DESC;我希望查詢的輸出是 id search_tag relevance_rank 11 car,cards,food,code 2.1669161319732666 12 car,water,sky 2.1669161319732665 13 carrier,food,drink 2.1669161319732664，但實際輸出就像 id search_tag relevance_rank1 11 carrier,food,drink 2.1669161319732666 12 car,cards,food,code 2.1669161319732666 13 car,water,sky 2.1669161319732666
2 回答
叮當貓咪
TA貢獻1776條經驗 獲得超12個贊
該查詢將整個單詞的記錄排名更高，但car,cards排名與 just 相同，car因此它仍然不是最佳的。
SELECT id, search_tags,
MATCH(search_tags) AGAINST('car *') +
MATCH(search_tags) AGAINST('car') AS relevance_rank
FROM images
WHERE MATCH(search_tags) AGAINST('car *')
OR MATCH(search_tags) AGAINST('car')
ORDER BY 3 DESC
+----+---------------------+----------------+
| 身份證 | search_tags | 相關性_排名|
+----+---------------------+----------------+
| 12 | 汽車，卡片，食物，代碼| 0.1677478002 |
| 13 | 汽車，水，天空| 高分辨率照片| CLIPARTO 0.1677478002 |
| 11 | 載體，食物，飲料| 高分辨率照片| CLIPARTO 0.0093915509 |
+----+---------------------+----------------+
下一個查詢還會查看確切單詞在字符串中出現的次數。更好的結果，但可能有點太復雜了。
SELECT id, search_tags,
MATCH(search_tags) AGAINST('car *') +
MATCH(search_tags) AGAINST('car') AS relevance_rank,
LENGTH(search_tags) -
LENGTH(REPLACE(search_tags, 'car', space(LENGTH('car')) -1 )) AS occurences
FROM images
WHERE MATCH(search_tags) AGAINST('car *')
OR MATCH(search_tags) AGAINST('car')
ORDER BY 3 DESC, 4 DESC
+----+---------------------+-----+----- -------+
| 身份證 | search_tags | 相關性_排名| 發生|
+----+---------------------+-----+----- -------+
| 12 | 汽車，卡片，食物，代碼| 0.1677478002 | 2 |
| 13 | 汽車，水，天空| 高分辨率照片| CLIPARTO 0.1677478002 | 1 |
| 11 | 載體，食物，飲料| 高分辨率照片| CLIPARTO 0.0093915509 | 1 |
+----+---------------------+-----+----- -------+
慕蓋茨4494581
TA貢獻1850條經驗 獲得超11個贊
使用Union時應該工作：
SELECT
id,
search_tags,
MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," "," ") IN BOOLEAN MODE) AS relevance_rank,
1 as prio -- sort by priority first
FROM
images
WHERE
MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," "," ") IN BOOLEAN MODE)
ORDER BY relevance_rank DESC
UNION SELECT
id,
search_tags,
MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ") IN BOOLEAN MODE) AS relevance_rank,
2 as prio -- sort by priority first
FROM
images
WHERE
MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ") IN BOOLEAN MODE)
ORDER BY prio, relevance_rank DESC;
- 2 回答
- 0 關注
- 228 瀏覽
添加回答
舉報
0/150
提交
取消