sphinx实现联合查询几个例子

sphinx也就是一个像mysql数据库的工具了,我们可以在linux中使用sphinx来替换mysql了,下面小编整理了几个sphinx联合查询的语句,记得以前sphinx是不支持联合查询的,第一次接触sphinx,好像2010年初的时候,当时写过一篇关于sphinx安装的文章,sphinx mmseg mysql 中文分词,下面举例说明,sphinx的联合查询用法.

一,添加二张测试表和数据,代码如下:

1,users表和数据

1. mysql>descusers;
2. +----------+-------------+------+-----+---------+----------------+
3. |Field|Type|Null|Key|Default|Extra|
4. +----------+-------------+------+-----+---------+----------------+
5. |user_id|int(11)|NO|PRI|NULL|auto_increment|
6. |username|varchar(20)|NO||NULL||
7. +----------+-------------+------+-----+---------+----------------+
8. 2rowsinset(0.00sec)
9. --phpfensi.com
10. mysql>select*fromusers;
11. +------------+------------+
12. |user_id|username|
13. +------------+------------+
14. |1311895262|张三|
15. |1311895263|tank张二|
16. |1311895264|tank张一|
17. |1311895265|tank张|
18. +------------+------------+
19. 4rowsinset(0.00sec)

2,orders表和数据

1. mysql>descorders;
2. +--------------+-------------+------+-----+---------+----------------+
3. |Field|Type|Null|Key|Default|Extra|
4. +--------------+-------------+------+-----+---------+----------------+
5. |id|int(11)|NO|PRI|NULL|auto_increment|
6. |user_id|int(11)|NO||NULL||
7. |create_time|datetime|NO||NULL||
8. |product_name|varchar(20)|NO||NULL||
9. |summary|text|NO||NULL||
10. +--------------+-------------+------+-----+---------+----------------+
11. 5rowsinset(0.00sec)
13. mysql>select*fromorders;
14. +----+------------+---------------------+----------------+--------------+
15. |id|user_id|create_time|product_name|summary|
16. +----+------------+---------------------+----------------+--------------+
17. |9|1311895262|2014-08-0100:24:54|tankis坦克|技术总监|
18. |10|1311895263|2014-08-0100:24:54|tankis坦克|技术经理|
19. |11|1311895264|2014-08-0100:24:54|tankis坦克|DNB经理|
20. |12|1311895265|2014-08-0100:24:54|tankis坦克|运维总监|
21. +----+------------+---------------------+----------------+--------------+
22. 4rowsinset(0.00sec)

二,配置sphinx.conf,代码如下:

1. sourceorder
2. {
3. type=mysql
4. sql_host=localhost
5. sql_user=root
6. sql_pass=
7. sql_db=test
8. sql_query_pre=SETNAMESutf8
9. sql_query=＼
10. SELECTa.id,a.user_id,b.username,UNIX_TIMESTAMP(a.create_time)AScreate_time,a.product_name,a.summary＼
11. FROMordersaleftjoinusersbona.user_id=b.user_id
12. sql_attr_uint=user_id
13. sql_field_string=username
14. sql_field_string=product_name
15. sql_attr_timestamp=create_time
16. sql_ranged_throttle=0
17. sql_query_info=SELECT*FROMordersWHEREid=$id
18. }
20. indexmyorder
21. {
22. source=order
23. path=/usr/local/sphinx/var/data/myorder
24. docinfo=extern
25. mlock=0
26. morphology=none
27. min_word_len=1
28. charset_dictpath=/usr/local/mmseg3/etc/
29. charset_type=zh_cn.utf-8
30. ngram_len=0
31. html_strip=0
32. }

注意:在这里a.user_id = b.user_id,等号二边一定要有空格,不然就会报错.

三,重启sphinx,代码如下:

1. #pkillsearchd
2. #/usr/local/sphinx/bin/indexer--config/usr/local/sphinx/etc/sphinx.conf--all
3. #/usr/local/sphinx/bin/searchd--config/usr/local/sphinx/etc/sphinx.conf

四,测试sphinx,代码如下:

1. [root@localhostetc]#mysql-h127.0.0.1-P9306//登录sphinx,9306端口,不是真实的mysql
2. WelcometotheMySQLmonitor.Commandsendwith;or＼g.
3. YourMySQLconnectionidis1
4. Serverversion:1.11-id64-dev(r2540)
6. Copyright(c)2000,2013,Oracleand/oritsaffiliates.Allrightsreserved.
8. OracleisaregisteredtrademarkofOracleCorporationand/orits
9. affiliates.Othernamesmaybetrademarksoftheirrespective
10. owners.
12. Type'help;'or'＼h'forhelp.Type'＼c'toclearthecurrentinputstatement.
14. mysql>select*frommyorderwherematch('张');
15. +------+--------+------------+------------+-------------+----------------+
16. |id|weight|user_id|username|create_time|product_name|
17. +------+--------+------------+------------+-------------+----------------+
18. |9|1304|1311895262|张三|1406823894|tankis坦克|
19. |10|1304|1311895263|tank张二|1406823894|tankis坦克|
20. |11|1304|1311895264|tank张一|1406823894|tankis坦克|
21. |12|1304|1311895265|tank张|1406823894|tankis坦克|
22. +------+--------+------------+------------+-------------+----------------+
23. 4rowsinset(0.01sec)
25. mysql>select*frommyorderwherematch('张三');
26. +------+--------+------------+----------+-------------+----------------+
27. |id|weight|user_id|username|create_time|product_name|
28. +------+--------+------------+----------+-------------+----------------+
29. |9|2500|1311895262|张三|1406823894|tankis坦克|
30. +------+--------+------------+----------+-------------+----------------+
31. 1rowinset(0.00sec)