Home >Database >Mysql Tutorial >coreseek sphinx 创建表和索引

coreseek sphinx 创建表和索引

WBOYOriginal: 2016-06-07 16:38:101023browse

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

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

[root@localhost tank]# mysql -h 127.0.0.1 -P 9306      //不是真的连接mysql，而连接了sphinx index
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 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 index 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 [...] coreseek sphinx 创建表和索引

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：Oracle PRM-DUL成功帮助安徽某政府机关恢复数据Next article：YARN作业运行机制

See more

coreseek sphinx 创建表和索引

Related articles