coreseek sphinx 創建表和索引的語句

下面來看一個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 --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) 
0