當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

windows7使用Sphinx+PHP+MySQL详细介绍

發布時間：2023/11/29 数据库 42 豆豆

生活随笔收集整理的這篇文章主要介紹了 windows7使用Sphinx+PHP+MySQL详细介绍小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

安裝（Windows）

1.官方下載

Sphinx下載地址：下載

2.解壓并重命名

此處下載版本為3.0.3，將 sphinx 文件夾命名為sphinx

3.文件夾目錄介紹

sphinx --api(各語言支持的api) --bin（二進制程序） --doc（文檔說明） --etc（配置文件：conf/sql） --misc --src # 手動創建以下兩個文件夾 --data --log

4.設置配置文件

(1)將sphinx/etc/sphinx-min.conf.dist文件復制到sphinx/bin/目錄下，并重命名為sphinx.conf

注：sphinx/etc/sphinx.conf.dist為帶注釋的詳細的

(2)設置配置項

主要是以下為配置函數：

source src1{} --- 連接數據庫的基本配置

# 連接的數據庫類型 type = mysql # 連接的數據庫主機 sql_host = localhost # 數據庫連接的用戶名，默認為test sql_user = root # 數據庫連接的密碼，默認為空 sql_pass =123123 # 連接的數據庫名稱，默認為test sql_db = test # 連接數據庫的端口號，默認為3306 sql_port = 3306 # 操作的數據表執行的查詢語句 sql_query = \SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title, content \FROM documents

index test1{}

# 索引數據存放目錄，默認為/var/data/test1 path = D:\Service\sphinx\data\test1 # 設置中文匹配 min_word_len = 1 charset_type = utf-8 # 指定utf-8的編碼表 charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F min_word_len = 1 min_prefix_len = 0 min_infix_len = 1 # 開啟中文分詞支持 ngram_len = 1 # 需要分詞的字符 ngram_chars = U+3000..U+2FA1F

index test1stemmed : test1{}

# 主要需要修改的配置項，默認為/var/data/test1stemmed path = D:\Service\sphinx\data\test1stemmed

index rt{}

# 主要需要修改的配置項，默認為/var/data/rt path = D:\Service\sphinx\data\rt # 指定對哪些字段進行匹配 rt_field = name rt_field = ename rt_field = setmeal rt_field = category rt_field = country rt_field = traffic rt_field = body# rt_attr_uint = offerid

searchd{}

# 自定義日志文件位置 log = D:\Service\sphinx\log\searchd.log query_log = D:\Service\sphinx\log\query.log pid_file = D:\Service\sphinx\log\searchd.pid

以下幾項不需要修改默認值，即可直接使用

source src1throttled : src1{}

分布式索引的相關配置，沒有則可以不修改

index dist1{}
indexer{}
common{}

5.操作數據庫，導入樣例數據

(1)進入到mysql命令行，執行命令

D:\phpStudy\PHPTutorial\MySQL\bin>mysql -uroot -p Enter password: ************* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.5.53 MySQL Community Server (GPL)mysql> use test;# 恢復樣例數據到數據庫 mysql> source /D:\Service\sphinx\etc/eaxmple.sql# 新增兩個數據表，documents和tags mysql> show tables; documents tags

6.生成索引文件

cmd命令行進入到sphinx/bin/目錄下

# 生成索引文件 > indexer.exe --config sphinx.conf --all Sphinx 3.0.3-dev (commit facc3fb) Copyright (c) 2001-2018, Andrew Aksyonoff Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)using config file 'sphinx.conf'... WARNING: key 'docinfo' was permanently removed from Sphinx configuration. Refer to documentation for details. WARNING: key 'dict' was permanently removed from Sphinx configuration. Refer to documentation for details. WARNING: key 'mva_updates_pool' was permanently removed from Sphinx configuration. Refer to documentation for details. indexing index 'test1'... collected 4 docs, 0.0 MB sorted 0.0 Mhits, 100.0% done total 4 docs, 0.2 Kb total 1.0 sec, 0.2 Kb/sec, 3 docs/sec indexing index 'test1stemmed'... collected 4 docs, 0.0 MB sorted 0.0 Mhits, 100.0% done total 4 docs, 0.2 Kb total 1.0 sec, 0.2 Kb/sec, 3 docs/sec skipping non-plain index 'dist1'... skipping non-plain index 'rt'...

【注】新版sphinx的bin目錄下已經沒有search.exe程序，所以不能直接在命令行執行返回結果，只能使用api接口返回數據。

7.開啟搜索服務，保持后臺運行

> searchd.exe --pidfile[Tue May 15 09:02:14.690 2018] [7776] using config file './sphinx.conf'... listening on all interfaces, port=9312 listening on all interfaces, port=9306 Sphinx 3.0.3-dev (commit facc3fb) Copyright (c) 2001-2018, Andrew Aksyonoff Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

PHP開啟sphinx擴展

1.下載php_sphinx擴展: 前往

具體需要下載的版本需要查看phpinfo信息:

Architecture ==》x86/x64 PHP Extension Build ==》NTS/NS

下載并解壓后，將php_sphinx.dll文件放到php/ext目錄下

2.修改php.ini配置文件

# 在 Dynamic Extensions 列表中添加php_sphinx擴展 extension=php_sphinx.dll

修改后重啟apache服務

3.在phpinfo.php輸出的信息中查看sphinx擴展是否安裝成功

sphinx sphinx support enabled Version 1.3.2 Revision $Revision$

代碼實現

1.樣例數據表test.documents記錄：

id group_id group_id2 date_added title content1 1 5 2018-05-14 09:12:25 test one this is my test document number one. also checking search within phrases.2 1 6 2018-05-14 09:12:25 test two this is my test document number two3 2 7 2018-05-14 09:12:25 another doc this is another group4 2 8 2018-05-14 09:12:25 doc number four this is to test groups

2.PHP代碼實現

一般實現

<?php require('sphinxapi.php');$sphinx = new SphinxClient(); $sphinx->SetServer('localhost',9312); $sphinx->SetMatchMode(SPH_MATCH_ANY); $sphinx->SetArrayResult ( true ); $res = $sphinx->Query($_GET['key'],'*'); var_dump($res);

thinkphp5使用介紹

1.將sphinxapi.php文件放到extend目錄下

2.在控制器方法中使用（app/api/index）

public function test() {$sphinx = new \SphinxClient();// sphinx的主機名和端口$sphinx->SetServer('localhost',9312);// 匹配模式$sphinx->SetMatchMode(SPH_MATCH_ANY);// 設置返回結果集為php數組格式$sphinx->SetArrayResult ( true );$res = $sphinx->Query(input('key'),'*');var_dump($res); }

3.url訪問：
http://localhost/mypro/api/index/test?key=test

4.輸出數據

D:\web\COD\api\application\api\controller\Index.php:21: array (size=10)'error' => string '' (length=0)'warning' => string '' (length=0)'status' => int 0'fields' => // 查詢顯示的字段名array (size=2)0 => string 'title' (length=5)1 => string 'content' (length=7)'attrs' => array (size=2)'group_id' => string '1' (length=1)'date_added' => string '2' (length=1)'matches' => // 匹配的結果，返回匹配記錄的id和權重（權重越大，匹配條件越多）array (size=3)0 => array (size=3)'id' => string '1' (length=1)'weight' => int 2421'attrs' => array (size=2)...1 => array (size=3)'id' => string '2' (length=1)'weight' => int 2421'attrs' => array (size=2)...2 => array (size=3)'id' => string '4' (length=1)'weight' => int 1442'attrs' => array (size=2)...'total' => int 3'total_found' => int 3'time' => float 0'words' => array (size=1)'test' => array (size=2)'docs' => int 6'hits' => int 10

在ThinkPHP5項目中應用

1.修改配置信息sphinx/bin/sphinx.conf

source src1{ # 省略其他配置 sql_user = root sql_pass = 123123 sql_db = shopMall sql_query = \SELECT id,offerid, name, ename, setmeal, category, country, traffic, os, body, inventory_title, shop \FROM i_offer sql_attr_uint = offerid # 省略其他配置 } index rt {type = rtpath = D:\Service\sphinx\data\rtrt_field = namert_field = enamert_field = setmealrt_field = categoryrt_field = countryrt_field = trafficrt_field = bodyrt_attr_uint = offerid }

2.生成索引，并開啟searchd服務

# 生成項目索引 sphinx/bin/indexer.exe --config sphinx.conf --all# 開啟服務 &表示后臺開啟，不用保持窗口執行狀態 sphinx/bin/searchd.exe &

3.程序實現

sphinx查詢返回的結果并不是我們需要的顯示結果，所以還需要對結果進行處理，從而獲取到我們需要的結果。

默認sphinx返回的數據中包含id信息是和數據記錄的信息是相關的，所以我們需要通過id到數據庫中查詢相關信息。

4.測試實現

訪問url：
http://localhost/mypro/api/index/test?key=官方

返回結果：

D:\web\COD\api\application\api\controller\Index.php:22: array (size=10)'error' => string '' (length=0)'warning' => string '' (length=0)'status' => int 0'fields' => array (size=10)0 => string 'name' (length=4)1 => string 'ename' (length=5)2 => string 'setmeal' (length=7)3 => string 'category' (length=8)4 => string 'country' (length=7)5 => string 'traffic' (length=7)6 => string 'os' (length=2)7 => string 'body' (length=4)8 => string 'inventory_title' (length=15)9 => string 'shop' (length=4)'attrs' => array (size=1)'offerid' => string '1' (length=1)'matches' => array (size=6)0 => array (size=3)'id' => string '36' (length=2)'weight' => int 4667'attrs' => array (size=1)...1 => array (size=3)'id' => string '19' (length=2)'weight' => int 2611'attrs' => array (size=1)...// 此處省略部分數據'total' => int 6'total_found' => int 6'time' => float 0'words' => array (size=2)'官' => array (size=2)'docs' => int 14'hits' => int 16'方' => array (size=2)'docs' => int 70'hits' => int 94

對結果進行處理

public function test() {$s = new \SphinxClient;$s->setServer("localhost", 9312);// 作為數組返回$s->SetArrayResult(true);// 匹配格式任意匹配$s->setMatchMode(SPH_MATCH_ANY);$s->setMaxQueryTime(3);// input()表示接收用戶傳過來的數據$result = $s->query(input('key'),'*');// 避免沒有匹配記錄時報錯if(empty($result['matches'])) {return null;}$result = $result['matches'];// 返回數組中指定的id列, 返回結果為單列數組$result = array_column($result, 'id');$list = model('offer')->field('offerid, name, ename, setmeal, category, country, traffic, os, body, inventory_title, shop')->where(array('id' => array('in', $result)))->select();return json($list); }

返回結果

[{offerid: 2332302,name: "【官方站】減震隱形增高鞋墊（安全有效~秒增高5公分~）",ename: "zenggaoxiedian",setmeal: "日韓超夯，氣墊隱形增高墊，輕鬆增高5公分，透氣減震，抗菌防臭，藝人最愛！【可拆分，自由裁剪，均碼35-44可用】【超殺998三雙】",category: "[{"id":6,"name":"其他"},{"id":7,"name":"商城"},{"id":8,"name":"家庭用品\n"}]",country: "[{"id":11,"name":"American Samoa"},{"id":1,"name":"Andorra"},{"id":8,"name":"Angola"},{"id":5,"name":"Anguilla"},{"id":10,"name":"Argentina"},{"id":7,"name":"Armenia"},{"id":12,"name":"Austria"}]",traffic: "[{"id":2,"name":"16+"},{"id":3,"name":"3G\/4G"},{"id":4,"name":"Adult"}]",os: "[{"id":1,"name":"3DS System Software"},{"id":2,"name":"Android"},{"id":13,"name":"BeOS"},{"id":16,"name":"CentOS"}]",body: "123123123",inventory_title: "隱形增高鞋墊B",shop: "[{"userid":77912776,"name":"myShop"}]"},{offerid: 3308032,name: "【官方站】電熱造型梳",ename: "zaoxingshu",setmeal: "長/短髮都適用！好梳好上手！亂翹髮尾一秒聽話！【人氣爆紅款美髮救星】限時特價，加NT$300再得1件！！！",category: "[{"id":2,"name":"美容"},{"id":4,"name":"日用品"},{"id":6,"name":"其他"},{"id":8,"name":"家庭用品\n"}]",country: "[{"id":6,"name":"Albania"},{"id":4,"name":"Antigua And Barbuda"}]",traffic: "[{"id":3,"name":"3G\/4G"},{"id":5,"name":"Adult Content"},{"id":6,"name":"App Discovery Traffic"}]",os: "[{"id":3,"name":"Android with AOKP"},{"id":5,"name":"Android with Cyanogen Mod"},{"id":6,"name":"Android with LiquidSmooth"},{"id":7,"name":"Android with MIUI"}]",body: "123123123"inventory_title: "NOVA多功能卷髮棒B",shop: "[{"userid":77912776,"name":"myShop"}]"} ]

支持簡體中文、繁體中文和英文的檢索。

暫且實現如此。

參考連接：

PHP官方手冊使用Sphinx介紹：

http://www.php.net/manual/zh/book.sphinx.php

sphinx安裝：

https://blog.csdn.net/huang2017/article/details/69665057

https://blog.csdn.net/huang2017/article/details/69666154

將sphinx服務添加到windows服務：

./searchd.exe --install -c sphinx.conf --servicename s

https://blog.csdn.net/design321/article/details/8895712

sphinx使用：

https://blog.csdn.net/u010837612/article/details/70827481

中文支持（linux系統）

http://www.xuejiehome.com/blread-1283.html

中文支持（windows系統）

http://www.phpernote.com/php-template-framework/284.html

其他

https://my.oschina.net/guyson/blog/283576

轉載于:https://www.cnblogs.com/zqunor/p/9045267.html

總結

以上是生活随笔為你收集整理的windows7使用Sphinx+PHP+MySQL详细介绍的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。