下面来看一个coreseek sphinx 创建表和索引的语句例子,希望此文章能帮助到各位理解coreseek sphinx数据库.
前面说了,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
- # /usr/local/sphinx/bin/searchd
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
- # /usr/local/sphinx/bin/searchd
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)
- 0
|
