SPHINX（搜索）：在查询中伪造 sql

发布于 2024-10-14 02:59:03 字数 1635 浏览 3 评论 0原文

我对 sphinx 还很陌生，所以我有一个问题。

假设我有一个名为 tv_releases 的表，

我的 sphinx.conf 包含（一小部分）：

source tv_releases
{
    type            = mysql

    sql_host        = localhost
    sql_user        = ---------
    sql_pass        = ---------
    sql_db          = --------
    sql_port        = 3306  

    sql_query_pre = SET NAMES utf8

    #ingore for example ( delta updates ).
    sql_query_pre = REPLACE INTO title_counter SELECT 3, MAX(id) FROM tv_releases

    sql_query = SELECT id,rel ,tv_id,total_subs FROM tv_releases\

     #ignore for example ( delta updates).
     WHERE id<=( SELECT max_doc_id FROM title_counter WHERE counter_id=3 )

    sql_attr_uint = total_subs
    sql_attr_uint = tv_id
}

索引很好，查询也很好。

现在 sphinxapi ( php ) （部分）：

$sp = new SphinxClient();
---...

$sp->SetMatchMode(SPH_MATCH_ALL);
$sp->SetArrayResult(true);
$sp->SetLimits ( 0, 100);
$sp->SetSortMode ( SPH_SORT_EXTENDED, "@relevance DESC, ....more options" );



  $results = $sp->Query('SEARCHTERM', 'tv_releases');

现在我的问题：

假设我有以下版本（在表中称为 rel ）。

rel 1 : Name s01e02 Format-extradata
rel 2 : Name s01e03 Format-extradata
rel 3 : Name s02e05 Format-extradata
rel 4 : Name s02e06 Format-extradata
rel 5 : Name s03e06 Format-extradata

当我的 SEARCHTERM 为“Name S01”时，

我希望匹配的版本 ( rel) （按 Relevance 排序）

将从包含 Name s01e** 的所有版本开始

问题是，它不会这样做。一个简单的像 sql 中的“Name s01%”就可以做到这一点，但 sphinx 的重点是速度。

有人有关于如何在 sphinx 中完成所需（搜索）结果的解决方案吗？

干杯

原文

I am pretty new to sphinx, so I have a question about it.

Let's say I have a table called tv_releases

My sphinx.conf contains (small portion) :

source tv_releases
{
    type            = mysql

    sql_host        = localhost
    sql_user        = ---------
    sql_pass        = ---------
    sql_db          = --------
    sql_port        = 3306  

    sql_query_pre = SET NAMES utf8

    #ingore for example ( delta updates ).
    sql_query_pre = REPLACE INTO title_counter SELECT 3, MAX(id) FROM tv_releases

    sql_query = SELECT id,rel ,tv_id,total_subs FROM tv_releases\

     #ignore for example ( delta updates).
     WHERE id<=( SELECT max_doc_id FROM title_counter WHERE counter_id=3 )

    sql_attr_uint = total_subs
    sql_attr_uint = tv_id
}

Indexing is fine , querying also.

Now sphinxapi ( php ) (portion) :

$sp = new SphinxClient();
---...

$sp->SetMatchMode(SPH_MATCH_ALL);
$sp->SetArrayResult(true);
$sp->SetLimits ( 0, 100);
$sp->SetSortMode ( SPH_SORT_EXTENDED, "@relevance DESC, ....more options" );



  $results = $sp->Query('SEARCHTERM', 'tv_releases');

Now My question :

Let's say I have the following releases ( called rel in table ).

rel 1 : Name s01e02 Format-extradata
rel 2 : Name s01e03 Format-extradata
rel 3 : Name s02e05 Format-extradata
rel 4 : Name s02e06 Format-extradata
rel 5 : Name s03e06 Format-extradata

When my SEARCHTERM is "Name S01"

I want that the matching releases ( rel) ( ordered by Relevance )

will start with all the releases containing Name s01e**

The problem is , it will not do this.
A simple Like 'Name s01%' in sql will do that , but the whole point of sphinx was the speed.

Someone has a solution on how to accomplish the needed (search) results in sphinx ?

Cheers

分享到QQ

分享到微博