sphinx實現聯合查詢幾個例子

sphinx也就是一個像mysql數據庫的工具了，我們可以在linux中使用sphinx來替換mysql了，下面小編整理了幾個sphinx聯合查詢的語句。

記得以前sphinx是不支持聯合查詢的，第一次接觸sphinx，好像2010年初的時候，當時寫過一篇關於sphinx安裝的文章。sphinx mmseg mysql 中文分詞，下面舉例說明，sphinx的聯合查詢用法。

一，添加二張測試表和數據

 代碼如下 復制代碼 1,users表和數據
mysql> desc users; 
+----------+-------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+----------+-------------+------+-----+---------+----------------+ 
| user_id | int(11) | NO | PRI | NULL | auto_increment | 
| username | varchar(20) | NO | | NULL | | 
+----------+-------------+------+-----+---------+----------------+ 
2 rows in set (0.00 sec) 
 
mysql> select * from users; 
+------------+------------+ 
| user_id | username | 
+------------+------------+ 
| 1311895262 | 張三 | 
| 1311895263 | tank張二 | 
| 1311895264 | tank張一 | 
| 1311895265 | tank張 | 
+------------+------------+ 
4 rows in set (0.00 sec) 
 2,orders表和數據
mysql> desc orders; 
+--------------+-------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+--------------+-------------+------+-----+---------+----------------+ 
| id | int(11) | NO | PRI | NULL | auto_increment | 
| user_id | int(11) | NO | | NULL | | 
| create_time | datetime | NO | | NULL | | 
| product_name | varchar(20) | NO | | NULL | | 
| summary | text | NO | | NULL | | 
+--------------+-------------+------+-----+---------+----------------+ 
5 rows in set (0.00 sec) 
 
mysql> select * from orders; 
+----+------------+---------------------+----------------+--------------+ 
| id | user_id | create_time | product_name | summary | 
+----+------------+---------------------+----------------+--------------+ 
| 9 | 1311895262 | 2014-08-01 00:24:54 | tank is 坦克 | 技術總監 | 
| 10 | 1311895263 | 2014-08-01 00:24:54 | tank is 坦克 | 技術經理 | 
| 11 | 1311895264 | 2014-08-01 00:24:54 | tank is 坦克 | DNB經理 | 
| 12 | 1311895265 | 2014-08-01 00:24:54 | tank is 坦克 | 運維總監 | 
+----+------------+---------------------+----------------+--------------+ 
4 rows in set (0.00 sec) 

二，配置sphinx.conf

 代碼如下 復制代碼 source order 
{ 
 type = mysql 
 sql_host = localhost 
 sql_user = root 
 sql_pass = 
 sql_db = test 
 sql_query_pre = SET NAMES utf8 
 sql_query = ＼ 
 SELECT a.id, a.user_id,b.username, UNIX_TIMESTAMP(a.create_time) AS create_time, a.product_name, a.summary ＼ 
 FROM orders a left join users b on a.user_id = b.user_id 
 sql_attr_uint = user_id 
 sql_field_string = username 
 sql_field_string = product_name 
 sql_attr_timestamp = create_time 
 sql_ranged_throttle = 0 
 sql_query_info = SELECT * FROM orders WHERE id=$id 
} 
 
index myorder 
{ 
 source = order 
 path = /usr/local/sphinx/var/data/myorder 
 docinfo = extern 
 mlock = 0 
 morphology = none 
 min_word_len = 1 
 charset_dictpath = /usr/local/mmseg3/etc/ 
 charset_type = zh_cn.utf-8 
 ngram_len = 0 
 html_strip = 0 
} 

注意：在這裡a.user_id = b.user_id，等號二邊一定要有空格，不然就會報錯。
三，重啟sphinx

 代碼如下 復制代碼 # pkill searchd 
# /usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/sphinx.conf --all 
# /usr/local/sphinx/bin/searchd --config /usr/local/sphinx/etc/sphinx.conf 

四，測試sphinx

 代碼如下 復制代碼 [root@localhost etc]# mysql -h 127.0.0.1 -P 9306                     //登錄sphinx,9306端口，不是真實的mysql 
Welcome to the MySQL monitor. Commands end with ; or ＼g. 
Your MySQL connection id is 1 
Server version: 1.11-id64-dev (r2540) 
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '＼h' for help. Type '＼c' to clear the current input statement. 
 
mysql> select * from myorder where match('張'); 
+------+--------+------------+------------+-------------+----------------+ 
| id | weight | user_id | username | create_time | product_name | 
+------+--------+------------+------------+-------------+----------------+ 
| 9 | 1304 | 1311895262 | 張三 | 1406823894 | tank is 坦克 | 
| 10 | 1304 | 1311895263 | tank張二 | 1406823894 | tank is 坦克 | 
| 11 | 1304 | 1311895264 | tank張一 | 1406823894 | tank is 坦克 | 
| 12 | 1304 | 1311895265 | tank張 | 1406823894 | tank is 坦克 | 
+------+--------+------------+------------+-------------+----------------+ 
4 rows in set (0.01 sec) 
 
mysql> select * from myorder where match('張三'); 
+------+--------+------------+----------+-------------+----------------+ 
| id | weight | user_id | username | create_time | product_name | 
+------+--------+------------+----------+-------------+----------------+ 
| 9 | 2500 | 1311895262 | 張三 | 1406823894 | tank is 坦克 | 
+------+--------+------------+----------+-------------+----------------+ 
1 row in set (0.00 sec)