coreseek sphinx 创建表和索引

前面说了，coreseek sphinx mmseg mysql等的安装，下面说一下怎么使用。 一,coreseek sphinx启动后，会多出一个端口，并且可以像mysql一样登录，但不是登录mysql [root@localhost tank]# mysql -h 127.0.0.1 -P 9306 //不是真的连接mysql，而连接了sphinx in

前面说了，coreseek sphinx mmseg mysql等的安装，下面说一下怎么使用。

一,coreseek sphinx启动后，会多出一个端口，并且可以像mysql一样登录，但不是登录mysql

[root@localhost tank]# mysql -h 127.0.0.1 -P 9306      //不是真的连接mysql，而连接了sphinx indexWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 1.11-id64-dev (r2540)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark o<p style="color:transparent">本文来源gao!%daima.com搞$代*!码$网3</p>f Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select * from tank_test where match('坦克') ;   //这种写法，根原装的sphinx不一样+------+--------+------------+------+| id   | weight | user_id    | u_id |+------+--------+------------+------+|    3 |   2230 | 1311895260 |   62 ||    5 |   2230 | 1311895260 |   33 ||    4 |   1304 | 1311895262 |    0 ||    6 |   1304 | 1311895262 |   34 |+------+--------+------------+------+4 rows in set (0.00 sec)mysql> show META;     //上次检索的信息+---------------+-------+| Variable_name | Value |+---------------+-------+| total         | 3     || total_found   | 3     || time          | 0.000 || keyword[0]    | test  || docs[0]       | 3     || hits[0]       | 5     |+---------------+-------+6 rows in set (0.00 sec)mysql> show tables;    //这里的表其实不是真表，也不是create table创建出来的，是sphinx索引+--------------+-------------+| Index        | Type        |+--------------+-------------+| dist1        | distributed || myorder      | local       || rt           | rt          || tank_test    | rt          || test1        | local       || test1stemmed | local       |+--------------+-------------+6 rows in set (0.00 sec)

二，创建sphinx索引

1,修改/usr/local/sphinx/etc/sphinx.conf

# vim /usr/local/sphinx/etc/sphinx.conf   //添加以下内容index tank_test{ type            = rt path            = /usr/local/sphinx/var/data/rt charset_dictpath     = /usr/local/mmseg3/etc/ charset_type         = zh_cn.utf-8 ngram_len            = 0 rt_field        = name rt_field        = title rt_field        = sub_title rt_attr_uint        = user_id rt_attr_uint        = uid}

在这里要注意，rt_field是检索字段，rt_attr_uint是返回字段

2,重启sphinx

# pkill -9 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

3，插入数据，并查看

mysql> show tables;+--------------+-------------+| Index        | Type        |+--------------+-------------+| dist1        | distributed || rt           | rt          || tank_test    | rt          |      //新增加的索引| test1        | local       || test1stemmed | local       |+--------------+-------------+5 rows in set (0.00 sec)mysql> desc tank_test;+-----------+---------+| Field     | Type    |+-----------+---------+| id        | bigint  || name      | field   || title     | field   || sub_title | field   || user_id   | integer || u_id      | integer |+-----------+---------+6 rows in set (0.00 sec)mysql> insert into tank_test values (3,'坦克','tank is 坦克','技术总监',1311895260,33);mysql> insert into tank_test values (4,'tank张','tank is 坦克','技术总监',1311895262,34);mysql> select * from tank_test where match('坦克');    //匹配搜索的字段是rt_field+------+--------+------------+------+| id   | weight | user_id    | u_id |                 //返回的字段是rt_attr_uint+------+--------+------------+------+|    3 |   2230 | 1311895260 |   33 ||    4 |   1304 | 1311895262 |   34 |+------+--------+------------+------+2 rows in set (0.00 sec)

id和weight是系统自带的返回字段

到这儿索引就创建好了，show tables的时候是可以看新建的tank_test，用phpmyadmin或者其他mysql数据库连接工具根本看不到，原因是他根本不是真实的表。sphinx到底能不能用真实的表呢？

三，创建表，并添加索引

1,创建真实的表,插入数据

CREATE TABLE IF NOT EXISTS `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL , `username` varchar(20) NOT NULL, `create_time` datetime NOT NULL, `product_name` varchar(20) NOT NULL, `summary` text NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;INSERT INTO  `orders` (`user_id` ,`username` ,`create_time` ,`product_name` ,`summary`) VALUES('1311895262','张三','2014-08-01 00:24:54','tank is 坦克','技术总监'),('1311895263','tank张二','2014-08-01 00:24:54','tank is 坦克','技术经理'),('1311895264','tank张一','2014-08-01 00:24:54','tank is 坦克','DNB经理'),('1311895265','tank张','2014-08-01 00:24:54','tank is 坦克','运维总监');

在这里要注意，是连接mysql的3306端口，不是连接coreseek sphinx的9306

2，修改/usr/local/sphinx/etc/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 id, user_id, username, UNIX_TIMESTAMP(create_time) AS create_time, product_name, summary  \ FROM orders sql_attr_uint        = user_id 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}

3,重启sphinx

# pkill -9 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

4，切换到9306，检索测试

mysql> show tables;+--------------+-------------+| Index        | Type        |+--------------+-------------+| dist1        | distributed || myorder      | local       || rt           | rt          || tank_test    | rt          || test1        | local       || test1stemmed | local       |+--------------+-------------+6 rows in set (0.00 sec)mysql> desc myorder;+--------------+-----------+| Field        | Type      |+--------------+-----------+| id           | bigint    || username     | field     || product_name | field     || summary      | field     || user_id      | integer   || create_time  | timestamp |+--------------+-----------+6 rows in set (0.00 sec)mysql> select * from myorder where match('坦克');+------+--------+------------+-------------+| id   | weight | user_id    | create_time |+------+--------+------------+-------------+|    5 |   1304 | 1311895262 |  1407081600 ||    6 |   1304 | 1311895263 |  1406823894 ||    7 |   1304 | 1311895264 |  1406823894 ||    8 |   1304 | 1311895265 |  1406823894 |+------+--------+------------+-------------+4 rows in set (0.00 sec)

前面说了，coreseek sphinx mmseg mysql等的安装，下面说一下怎么使用。一,coreseek sphinx启动后，会多出一个端口，并且可以像mysql一样登录，但不是登录mysql[root@localhost tank]# mysql -h 127.0.0.1 -P 9306 //不是真的连接mysql，而连接了sphinx indexWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server 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 […]

喜欢 (0)赏

[搞代码]

分享 (0)