coreseek sphinx 创建表和索引的语句

小潘seo 重庆小潘seo博客和你一起学习SEO知识,共同分享SEO优化~

下面来看一个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

相关广告
  • coreseek sphinx 创建表和索引的语句 coreseek sphinx 创建表和索引的语句 coreseek sphinx 创建表和索引的语句
相关阅读

coreseek sphinx 创建表和索引的语句

2019/10/10 17:37:54 | 谷歌SEO算法 | 小明SEO博客