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)
  12. mysql>select*fromorders;
  13. +----+------------+---------------------+----------------+--------------+
  14. |id|user_id|create_time|product_name|summary|
  15. +----+------------+---------------------+----------------+--------------+
  16. |9|1311895262|2014-08-0100:24:54|tankis坦克|技术总监|
  17. |10|1311895263|2014-08-0100:24:54|tankis坦克|技术经理|
  18. |11|1311895264|2014-08-0100:24:54|tankis坦克|DNB经理|
  19. |12|1311895265|2014-08-0100:24:54|tankis坦克|运维总监|
  20. +----+------------+---------------------+----------------+--------------+
  21. 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. }
  19. indexmyorder
  20. {
  21. source=order
  22. path=/usr/local/sphinx/var/data/myorder
  23. docinfo=extern
  24. mlock=0
  25. morphology=none
  26. min_word_len=1
  27. charset_dictpath=/usr/local/mmseg3/etc/
  28. charset_type=zh_cn.utf-8
  29. ngram_len=0
  30. html_strip=0
  31. }

注意:在这里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)
  5. Copyright(c)2000,2013,Oracleand/oritsaffiliates.Allrightsreserved.
  6. OracleisaregisteredtrademarkofOracleCorporationand/orits
  7. affiliates.Othernamesmaybetrademarksoftheirrespective
  8. owners.
  9. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
  10. mysql>select*frommyorderwherematch('张');
  11. +------+--------+------------+------------+-------------+----------------+
  12. |id|weight|user_id|username|create_time|product_name|
  13. +------+--------+------------+------------+-------------+----------------+
  14. |9|1304|1311895262|张三|1406823894|tankis坦克|
  15. |10|1304|1311895263|tank张二|1406823894|tankis坦克|
  16. |11|1304|1311895264|tank张一|1406823894|tankis坦克|
  17. |12|1304|1311895265|tank张|1406823894|tankis坦克|
  18. +------+--------+------------+------------+-------------+----------------+
  19. 4rowsinset(0.01sec)
  20. mysql>select*frommyorderwherematch('张三');
  21. +------+--------+------------+----------+-------------+----------------+
  22. |id|weight|user_id|username|create_time|product_name|
  23. +------+--------+------------+----------+-------------+----------------+
  24. |9|2500|1311895262|张三|1406823894|tankis坦克|
  25. +------+--------+------------+----------+-------------+----------------+
  26. 1rowinset(0.00sec)

相关广告
  • sphinx实现联合查询几个例子 sphinx实现联合查询几个例子 sphinx实现联合查询几个例子
相关阅读

sphinx实现联合查询几个例子

2019/10/10 17:32:11 | 谷歌SEO算法 | 1号店SEO