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) --phpfensi.com 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 }
[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)。
