coreseek sphinx 创建表和索引的语句

下面来看一个coreseek sphinx 创建表和索引的语句例子,希望此文章能帮助到各位理解coreseek sphinx数据库.

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

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

1. [root@localhosttank]#mysql-h127.0.0.1-P9306//不是真的连接mysql，而连接了sphinxindex
3. WelcometotheMySQLmonitor.Commandsendwith;or＼g.
4. YourMySQLconnectionidis1
5. Serverversion:1.11-id64-dev(r2540)
7. Copyright(c)2000,2013,Oracleand/oritsaffiliates.Allrightsreserved.
9. OracleisaregisteredtrademarkofOracleCorporationand/orits
10. affiliates.Othernamesmaybetrademarksoftheirrespective
11. owners.
13. Type'help;'or'＼h'forhelp.Type'＼c'toclearthecurrentinputstatement.
15. mysql>select*fromtank_testwherematch('坦克');//这种写法，根原装的sphinx不一样
16. +------+--------+------------+------+
17. |id|weight|user_id|u_id|
18. +------+--------+------------+------+
19. |3|2230|1311895260|62|
20. |5|2230|1311895260|33|
21. |4|1304|1311895262|0|
22. |6|1304|1311895262|34|
23. +------+--------+------------+------+
24. 4rowsinset(0.00sec)
26. mysql>showMETA;//上次检索的信息
27. +---------------+-------+
28. |Variable_name|Value|
29. +---------------+-------+
30. |total|3|
31. |total_found|3|
32. |time|0.000|
33. |keyword[0]|test|
34. |docs[0]|3|
35. |hits[0]|5|
36. +---------------+-------+
37. 6rowsinset(0.00sec)
39. mysql>showtables;//这里的表其实不是真表，也不是createtable创建出来的，是sphinx索引
40. +--------------+-------------+
41. |Index|Type|
42. +--------------+-------------+
43. |dist1|distributed|
44. |myorder|local|
45. |rt|rt|
46. |tank_test|rt|
47. |test1|local|
48. |test1stemmed|local|
49. +--------------+-------------+
50. 6rowsinset(0.00sec)

二，创建sphinx索引

1,修改/usr/local/sphinx/etc/sphinx.conf,代码如下:

1. #vim/usr/local/sphinx/etc/sphinx.conf//添加以下内容
3. indextank_test
4. {
5. type=rt
6. path=/usr/local/sphinx/var/data/rt
7. charset_dictpath=/usr/local/mmseg3/etc/
8. charset_type=zh_cn.utf-8
9. ngram_len=0
10. rt_field=name
11. rt_field=title
12. rt_field=sub_title
13. rt_attr_uint=user_id
14. rt_attr_uint=uid
15. }

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

2,重启sphinx,代码如下:

1. #pkill-9searchd
3. #/usr/local/sphinx/bin/indexer--config/usr/local/sphinx/etc/sphinx.conf--all
4. #/usr/local/sphinx/bin/searchd--config/usr/local/sphinx/etc/sphinx.conf

3，插入数据,并查看,代码如下:

1. mysql>showtables;
2. +--------------+-------------+
3. |Index|Type|
4. +--------------+-------------+
5. |dist1|distributed|
6. |rt|rt|
7. |tank_test|rt|//新增加的索引
8. |test1|local|
9. |test1stemmed|local|
10. +--------------+-------------+
11. 5rowsinset(0.00sec)
13. mysql>desctank_test;
14. +-----------+---------+
15. |Field|Type|
16. +-----------+---------+
17. |id|bigint|
18. |name|field|
19. |title|field|
20. |sub_title|field|
21. |user_id|integer|
22. |u_id|integer|
23. +-----------+---------+
24. 6rowsinset(0.00sec)
26. mysql>insertintotank_testvalues(3,'坦克','tankis坦克','技术总监',1311895260,33);
28. mysql>insertintotank_testvalues(4,'tank张','tankis坦克','技术总监',1311895262,34);
30. mysql>select*fromtank_testwherematch('坦克');//匹配搜索的字段是rt_field
31. +------+--------+------------+------+
32. |id|weight|user_id|u_id|//返回的字段是rt_attr_uint
33. +------+--------+------------+------+
34. |3|2230|1311895260|33|
35. |4|1304|1311895262|34|
36. +------+--------+------------+------+
37. 2rowsinset(0.00sec)

id和weight是系统自带的返回字段,到这儿索引就创建好了,show tables的时候是可以看新建的tank_test,用phpmyadmin或者其他mysql数据库连接工具根本看不到,原因是他根本不是真实的表,sphinx到底能不能用真实的表呢？

三，创建表，并添加索引

1,创建真实的表,插入数据,代码如下:

1. CREATETABLEIFNOTEXISTS`orders`(
2. `id`int(11)NOTNULLAUTO_INCREMENT,
3. `user_id`int(11)NOTNULL,
4. `username`varchar(20)NOTNULL,
5. `create_time`datetimeNOTNULL,
6. `product_name`varchar(20)NOTNULL,
7. `summary`textNOTNULL,
8. PRIMARYKEY(`id`)
9. )ENGINE=InnoDBDEFAULTCHARSET=utf8AUTO_INCREMENT=1;
11. INSERTINTO`orders`(`user_id`,`username`,`create_time`,`product_name`,`summary`)VALUES
12. ('1311895262','张三','2014-08-0100:24:54','tankis坦克','技术总监'),
13. ('1311895263','tank张二','2014-08-0100:24:54','tankis坦克','技术经理'),
14. ('1311895264','tank张一','2014-08-0100:24:54','tankis坦克','DNB经理'),
15. ('1311895265','tank张','2014-08-0100:24:54','tankis坦克','运维总监');

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

2，修改/usr/local/sphinx/etc/sphinx.conf,添加以下内容:

1. sourceorder
2. {
3. type=mysql
4. sql_host=localhost
5. sql_user=root
6. sql_pass=
7. sql_db=test
8. sql_query_pre=SETNAMESutf8
9. sql_query=＼
10. SELECTid,user_id,username,UNIX_TIMESTAMP(create_time)AScreate_time,product_name,summary＼
11. FROMorders
12. sql_attr_uint=user_id
13. sql_attr_timestamp=create_time
14. sql_ranged_throttle=0
15. sql_query_info=SELECT*FROMordersWHEREid=$id
16. }
18. indexmyorder
19. {
20. source=order
21. path=/usr/local/sphinx/var/data/myorder
22. docinfo=extern
23. mlock=0
24. morphology=none
25. min_word_len=1
26. charset_dictpath=/usr/local/mmseg3/etc/
27. charset_type=zh_cn.utf-8
28. ngram_len=0
29. html_strip=0
30. }

3,重启sphinx,代码如下:

1. #pkill-9searchd
3. #/usr/local/sphinx/bin/indexer--config/usr/local/sphinx/etc/sphinx.conf--all
4. #/usr/local/sphinx/bin/searchd--config/usr/local/sphinx/etc/sphinx.conf

4，切换到9306,检索测试,代码如下:

1. mysql>showtables;
2. +--------------+-------------+
3. |Index|Type|
4. +--------------+-------------+
5. |dist1|distributed|
6. |myorder|local|
7. |rt|rt|
8. |tank_test|rt|
9. |test1|local|
10. |test1stemmed|local|
11. +--------------+-------------+
12. 6rowsinset(0.00sec)
14. mysql>descmyorder;
15. +--------------+-----------+
16. |Field|Type|
17. +--------------+-----------+
18. |id|bigint|
19. |username|field|
20. |product_name|field|
21. |summary|field|
22. |user_id|integer|
23. |create_time|timestamp|
24. +--------------+-----------+
25. 6rowsinset(0.00sec)
27. mysql>select*frommyorderwherematch('坦克');
28. +------+--------+------------+-------------+
29. |id|weight|user_id|create_time|
30. +------+--------+------------+-------------+
31. |5|1304|1311895262|1407081600|
32. |6|1304|1311895263|1406823894|
33. |7|1304|1311895264|1406823894|
34. |8|1304|1311895265|1406823894|
35. +------+--------+------------+-------------+
36. 4rowsinset(0.00sec)
37. 0//开源代码phpfensi.com