sphinx mysql连表查询_sphinx

这是sphinx.conf 的内容 source src1{

type = mysql

sql_host = localhost

sql_user = root

sql_pass = 1goodlusslulU

sql_db = fangxinjia_sku

sql_port = 330...

记得以前sphinx是不支持联合查询的，第一次接触sphinx，好像2010年初的时候，当时写过一篇关于sphinx安装的文章。 sphinx mmseg mysql 中文分词 ，下面举例说明，sphinx的联合查询用法。 一，添加二张测试表和数据 1,users表和数据 mysql desc users;+------

记得以前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)