V2AS问路
sphinx with discuz
阅读原文时间：2023年07月15日阅读：1

安装sphinx：

sudo apt-get install sphinxsearch

配置：

source discuz
{
type = mysql

sql\_host                = xx.xx.xx.xx  
sql\_user                =  
sql\_pass                =  
sql\_db                  = discuz  
sql\_port                = 3306  # optional, default is 3306

}

source uc
{
type = mysql

sql\_host                = xx.xx.xx.xx  
sql\_user                =  
sql\_pass                =  
sql\_db                  = ucenter  
sql\_port                = 3306  # optional, default is 3306

}

source threads : discuz
{
sql_query_pre = SET NAMES utf8
sql_query_pre = REPLACE INTO sph_counter SELECT 1, MAX(tid) FROM pre_forum_thread
sql_query = SELECT t.tid AS id,t.tid,t.fid,t.subject,t.digest,t.displayorder,t.dateline,t.lastpost,t.recommends,t.replies,t.views \
FROM pre_forum_thread AS t \
WHERE t.displayorder >= 0 AND t.tid>=$start AND t.tid<=$end 

sql\_query\_range         = SELECT (SELECT MIN(tid) FROM pre\_forum\_thread),max\_doc\_id FROM sph\_counter WHERE counter\_id = 1  
sql\_range\_step          = 4096

sql\_attr\_uint           = fid  
sql\_attr\_uint           = tid  
sql\_attr\_uint           = digest  
sql\_attr\_uint           = displayorder  
sql\_attr\_timestamp      = dateline  
sql\_attr\_uint           = recommends  
sql\_attr\_uint           = replies  
sql\_attr\_timestamp      = lastpost  
sql\_attr\_uint           = views

sql\_query\_info          = SELECT \* FROM pre\_forum\_thread WHERE tid=$id

}

source threads_delta : threads
{
sql_query_pre = SET NAMES utf8
sql_query_range = SELECT max_doc_id + 1, (SELECT MAX(tid) FROM pre_forum_thread) FROM sph_counter WHERE counter_id = 1
}

index threads
{
source = threads
path = /var/lib/sphinxsearch/data/threads
docinfo = extern
mlock = 0
morphology = none
charset_type = utf-8
html_strip = 0
charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z,\
A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6,\
U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101,\
U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109,\
U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F,\
U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117,\
U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D,\
U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135,\
U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C,\
U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144,\
U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B,\
U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153,\
U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159,\
U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161,\
U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167,\
U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F,\
U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175,\
U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C,\
U+017C, U+017D->U+017E, U+017E, U+0410..U+042F->U+0430..U+044F, U+0430..U+044F,\
U+05D0..U+05EA, U+0531..U+0556->U+0561..U+0586, U+0561..U+0587, U+0621..U+063A, U+01B9,\
U+01BF, U+0640..U+064A, U+0660..U+0669, U+066E, U+066F, U+0671..U+06D3, U+06F0..U+06FF,\
U+0904..U+0939, U+0958..U+095F, U+0960..U+0963, U+0966..U+096F, U+097B..U+097F,\
U+0985..U+09B9, U+09CE, U+09DC..U+09E3, U+09E6..U+09EF, U+0A05..U+0A39, U+0A59..U+0A5E,\
U+0A66..U+0A6F, U+0A85..U+0AB9, U+0AE0..U+0AE3, U+0AE6..U+0AEF, U+0B05..U+0B39,\
U+0B5C..U+0B61, U+0B66..U+0B6F, U+0B71, U+0B85..U+0BB9, U+0BE6..U+0BF2, U+0C05..U+0C39,\
U+0C66..U+0C6F, U+0C85..U+0CB9, U+0CDE..U+0CE3, U+0CE6..U+0CEF, U+0D05..U+0D39, U+0D60,\
U+0D61, U+0D66..U+0D6F, U+0D85..U+0DC6, U+1900..U+1938, U+1946..U+194F, U+A800..U+A805,\
U+A807..U+A822, U+0386->U+03B1, U+03AC->U+03B1, U+0388->U+03B5, U+03AD->U+03B5,\
U+0389->U+03B7, U+03AE->U+03B7, U+038A->U+03B9, U+0390->U+03B9, U+03AA->U+03B9,\
U+03AF->U+03B9, U+03CA->U+03B9, U+038C->U+03BF, U+03CC->U+03BF, U+038E->U+03C5,\
U+03AB->U+03C5, U+03B0->U+03C5, U+03CB->U+03C5, U+03CD->U+03C5, U+038F->U+03C9,\
U+03CE->U+03C9, U+03C2->U+03C3, U+0391..U+03A1->U+03B1..U+03C1,\
U+03A3..U+03A9->U+03C3..U+03C9, U+03B1..U+03C1, U+03C3..U+03C9, U+0E01..U+0E2E,\
U+0E30..U+0E3A, U+0E40..U+0E45, U+0E47, U+0E50..U+0E59, U+A000..U+A48F, U+4E00..U+9FBF,\
U+3400..U+4DBF, U+20000..U+2A6DF, U+F900..U+FAFF, U+2F800..U+2FA1F, U+2E80..U+2EFF,\
U+2F00..U+2FDF, U+3100..U+312F, U+31A0..U+31BF, U+3040..U+309F, U+30A0..U+30FF,\
U+31F0..U+31FF, U+AC00..U+D7AF, U+1100..U+11FF, U+3130..U+318F, U+A000..U+A48F,\
U+A490..U+A4CF

#简单分词，只支持0和1，如果要搜索中文，请指定为1  
ngram\_len       = 1

#需要分词的字符，如果要搜索中文，去掉前面的注释  
ngram\_chars     = U+4E00..U+9FBF, U+3400..U+4DBF, U+20000..U+2A6DF, U+F900..U+FAFF,\\  
U+2F800..U+2FA1F, U+2E80..U+2EFF, U+2F00..U+2FDF, U+3100..U+312F, U+31A0..U+31BF,\\  
U+3040..U+309F, U+30A0..U+30FF, U+31F0..U+31FF, U+AC00..U+D7AF, U+1100..U+11FF,\\  
U+3130..U+318F, U+A000..U+A48F, U+A490..U+A4CF  
min\_infix\_len   = 1  
min\_word\_len    = 1

}

index threads_delta : threads
{
source = threads_delta
path = /var/lib/sphinxsearch/data/threads_delta
#morphology = stem_en
}

source members : uc
{
sql_query_pre = SET NAMES utf8
sql_query_pre = REPLACE INTO sph_counter SELECT 2, MAX(uid) FROM uc_members

sql\_query               = SELECT m.uid AS id, m.uid, m.username FROM uc\_members AS m WHERE m.uid>=$start AND m.uid<=$end

sql\_query\_range         = SELECT (SELECT MIN(uid) FROM uc\_members),max\_doc\_id FROM sph\_counter WHERE counter\_id = 2  
sql\_range\_step          = 4096

sql\_attr\_uint           = uid

sql\_query\_info          = SELECT \* FROM uc\_members WHERE uid=$id

}

source members_delta : members
{
sql_query_pre = SET NAMES utf8
sql_query_range = SELECT max_doc_id + 1, (SELECT MAX(uid) FROM uc_members) FROM sph_counter WHERE counter_id = 2
}

index members
{
source = members
path = /var/lib/sphinxsearch/data/members
docinfo = extern
mlock = 0
morphology = none
charset_type = utf-8
html_strip = 0
charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z,\
A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6,\
U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101,\
U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109,\
U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F,\
U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117,\
U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D,\
U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135,\
U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C,\
U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144,\
U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B,\
U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153,\
U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159,\
U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161,\
U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167,\
U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F,\
U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175,\
U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C,\
U+017C, U+017D->U+017E, U+017E, U+0410..U+042F->U+0430..U+044F, U+0430..U+044F,\
U+05D0..U+05EA, U+0531..U+0556->U+0561..U+0586, U+0561..U+0587, U+0621..U+063A, U+01B9,\
U+01BF, U+0640..U+064A, U+0660..U+0669, U+066E, U+066F, U+0671..U+06D3, U+06F0..U+06FF,\
U+0904..U+0939, U+0958..U+095F, U+0960..U+0963, U+0966..U+096F, U+097B..U+097F,\
U+0985..U+09B9, U+09CE, U+09DC..U+09E3, U+09E6..U+09EF, U+0A05..U+0A39, U+0A59..U+0A5E,\
U+0A66..U+0A6F, U+0A85..U+0AB9, U+0AE0..U+0AE3, U+0AE6..U+0AEF, U+0B05..U+0B39,\
U+0B5C..U+0B61, U+0B66..U+0B6F, U+0B71, U+0B85..U+0BB9, U+0BE6..U+0BF2, U+0C05..U+0C39,\
U+0C66..U+0C6F, U+0C85..U+0CB9, U+0CDE..U+0CE3, U+0CE6..U+0CEF, U+0D05..U+0D39, U+0D60,\
U+0D61, U+0D66..U+0D6F, U+0D85..U+0DC6, U+1900..U+1938, U+1946..U+194F, U+A800..U+A805,\
U+A807..U+A822, U+0386->U+03B1, U+03AC->U+03B1, U+0388->U+03B5, U+03AD->U+03B5,\
U+0389->U+03B7, U+03AE->U+03B7, U+038A->U+03B9, U+0390->U+03B9, U+03AA->U+03B9,\
U+03AF->U+03B9, U+03CA->U+03B9, U+038C->U+03BF, U+03CC->U+03BF, U+038E->U+03C5,\
U+03AB->U+03C5, U+03B0->U+03C5, U+03CB->U+03C5, U+03CD->U+03C5, U+038F->U+03C9,\
U+03CE->U+03C9, U+03C2->U+03C3, U+0391..U+03A1->U+03B1..U+03C1,\
U+03A3..U+03A9->U+03C3..U+03C9, U+03B1..U+03C1, U+03C3..U+03C9, U+0E01..U+0E2E,\
U+0E30..U+0E3A, U+0E40..U+0E45, U+0E47, U+0E50..U+0E59, U+A000..U+A48F, U+4E00..U+9FBF,\
U+3400..U+4DBF, U+20000..U+2A6DF, U+F900..U+FAFF, U+2F800..U+2FA1F, U+2E80..U+2EFF,\
U+2F00..U+2FDF, U+3100..U+312F, U+31A0..U+31BF, U+3040..U+309F, U+30A0..U+30FF,\
U+31F0..U+31FF, U+AC00..U+D7AF, U+1100..U+11FF, U+3130..U+318F, U+A000..U+A48F,\
U+A490..U+A4CF

    #简单分词，只支持0和1，如果要搜索中文，请指定为1  
    ngram\_len = 1

    #需要分词的字符，如果要搜索中文，去掉前面的注释  
    ngram\_chars = U+4E00..U+9FBF, U+3400..U+4DBF, U+20000..U+2A6DF, U+F900..U+FAFF,\\  
    U+2F800..U+2FA1F, U+2E80..U+2EFF, U+2F00..U+2FDF, U+3100..U+312F, U+31A0..U+31BF,\\  
    U+3040..U+309F, U+30A0..U+30FF, U+31F0..U+31FF, U+AC00..U+D7AF, U+1100..U+11FF,\\  
    U+3130..U+318F, U+A000..U+A48F, U+A490..U+A4CF,A..Z->a..z,a..z  
    min\_infix\_len  = 1  
    min\_word\_len  = 1

}

index members_delta : members
{
source = members_delta
path = /var/lib/sphinxsearch/data/members_delta
}

indexer
{
mem_limit = 256M
}

searchd
{
listen = 9312
listen = 9306:mysql41

log                 = /var/log/sphinxsearch/searchd.log  
query\_log           = /var/log/sphinxsearch/query.log  
read\_timeout        = 5  
client\_timeout      = 300  
max\_children        = 30  
pid\_file            = /var/run/sphinxsearch/searchd.pid  
max\_matches         = 1000  
seamless\_rotate     = 1  
preopen\_indexes     = 1  
unlink\_old          = 1  
mva\_updates\_pool    = 1M  
max\_packet\_size     = 8M  
max\_filters         = 256  
max\_filter\_values   = 4096  
max\_batch\_queries   = 32  
workers             = threads # for RT to work

}

--eof--

注意：

　　任何一项配置，如果在新的数据源块中指定了新值，继承的值都将被忽略，即使有多个定义（ multivalue）－－也就是说在一个块中如果某个类型的定义有多个，如果在新块中定义了该类型的一个新值，那么继承来的该类型的整个列表都将被忽略。所以discuz source中指定的 sql_query_pre = SET NAMES utf8对threads source不起作用。

启动：

searchd

查看是否启动：

# netstat -anp | grep searchd
tcp 0.0.0.0: 0.0.0.0:* LISTEN /searchd
tcp 0.0.0.0: 0.0.0.0:* LISTEN /searchd

建立索引：

indexer --all

查询：

root@ubuntu:/etc/sphinxsearch# search -i threads -q 中国
Sphinx 2.0.-release (r3135)
Copyright (c) -, Andrew Aksyonoff
Copyright (c) -, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinxsearch/sphinx.conf'…
index 'threads': query '中国 ': returned matches of total in 0.001 sec

displaying matches:
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Sun Jan :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Tue Mar :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Mon Mar :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Sun Mar :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Tue Apr :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Mon May :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Sun Jan :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Wed Jan :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Wed Feb :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Sun Jan :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Sat Jan :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Thu Jan :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Tue Apr :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Mon Feb :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Tue Apr :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Sat Apr :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Sun Feb :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Thu Feb :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Sun Mar :: , special=
. document=, weight=, tid=, digest=, displayorder=, authorid=, lastpost=Sat Apr :: , special=

words:
. '中': documents, hits
. '国': documents, hits

连接虚拟mysql

root@ubuntu:/etc/sphinxsearch# mysql -h127.0.0.1 -P9306
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.0.4-release (r3135)

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show tables;
+---------------+-------+
| Index | Type |
+---------------+-------+
| members | local |
| threads | local |
| threads_delta | local |
+---------------+-------+
3 rows in set (0.00 sec)

MySQL [(none)]> desc threads;
+--------------+-----------+
| Field | Type |
+--------------+-----------+
| id | integer |
| subject | field |
| tid | uint |
| fid | uint |
| digest | uint |
| displayorder | uint |
| dateline | timestamp |
| lastpost | timestamp |
| recommends | uint |
| replies | uint |
+--------------+-----------+
10 rows in set (0.02 sec)

参考：

http://sphinxsearch.com/docs/2.1.8/installing-debian.html

https://code.google.com/p/sphinxsearch/

https://code.google.com/p/sphinx-for-chinese/

Sphinx 之 Coreseek、Sphinx-for-chinaese、Sphinx+Scws 评测

中文全文检索Coreseek, Sphinx-for-Chinese, SCWS的使用对比

Sphinx中文分词详细安装配置及API调用实战手册

Sphinx PHP API Documentation

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章

OpenStack-T版+Ceph
【故障公告】一而再，再而三，三翻四复：数据库服务器 CPU 100%
《SQL与数据库基础》14. 存储过程 · 存储函数

4

《SQL与数据库基础》15. 触发器

5

《SQL与数据库基础》11. 索引

6

图书商城项目练习②后端服务Node/Express/Sqlite

7

19cRAC增删节点

8

solr 文档一

9

02.python线性数据结构

10

mysql零基础-2