sphinx实现联合查询几个例子
发布时间：2023-04-29 编辑：jiaochengji.com
教程集为您提供sphinx实现联合查询几个例子等资源，欢迎您收藏本站，我们将为您提供最新的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) | |
您可能感兴趣的文章：
php sphinx的简单例子
Sphinx/MySQL 协议支持与SphinxQL应用例子
MySQL集群亿级数据量并发访问解决方案测试
mysql 子查询与join性能对比
使用sphinx搜索子域名需注意的问题
mysql子查询的五种方式
php高性能接口怎么设计
设计高效合理的MySQL查询语句的建议
django查询表数据是否存在
mysql多表查询的几个小例子
[关闭]