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

二,创建sphinx索引

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

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

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

2,重启sphinx,代码如下:

  1. #pkill-9searchd
  2. #/usr/local/sphinx/bin/indexer--config/usr/local/sphinx/etc/sphinx.conf--all
  3. #/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)
  12. mysql>desctank_test;
  13. +-----------+---------+
  14. |Field|Type|
  15. +-----------+---------+
  16. |id|bigint|
  17. |name|field|
  18. |title|field|
  19. |sub_title|field|
  20. |user_id|integer|
  21. |u_id|integer|
  22. +-----------+---------+
  23. 6rowsinset(0.00sec)
  24. mysql>insertintotank_testvalues(3,'坦克','tankis坦克','技术总监',1311895260,33);
  25. mysql>insertintotank_testvalues(4,'tank张','tankis坦克','技术总监',1311895262,34);
  26. mysql>select*fromtank_testwherematch('坦克');//匹配搜索的字段是rt_field
  27. +------+--------+------------+------+
  28. |id|weight|user_id|u_id|//返回的字段是rt_attr_uint
  29. +------+--------+------------+------+
  30. |3|2230|1311895260|33|
  31. |4|1304|1311895262|34|
  32. +------+--------+------------+------+
  33. 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;
  10. INSERTINTO`orders`(`user_id`,`username`,`create_time`,`product_name`,`summary`)VALUES
  11. ('1311895262','张三','2014-08-0100:24:54','tankis坦克','技术总监'),
  12. ('1311895263','tank张二','2014-08-0100:24:54','tankis坦克','技术经理'),
  13. ('1311895264','tank张一','2014-08-0100:24:54','tankis坦克','DNB经理'),
  14. ('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. }
  17. indexmyorder
  18. {
  19. source=order
  20. path=/usr/local/sphinx/var/data/myorder
  21. docinfo=extern
  22. mlock=0
  23. morphology=none
  24. min_word_len=1
  25. charset_dictpath=/usr/local/mmseg3/etc/
  26. charset_type=zh_cn.utf-8
  27. ngram_len=0
  28. html_strip=0
  29. }

3,重启sphinx,代码如下:

  1. #pkill-9searchd
  2. #/usr/local/sphinx/bin/indexer--config/usr/local/sphinx/etc/sphinx.conf--all
  3. #/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)
  13. mysql>descmyorder;
  14. +--------------+-----------+
  15. |Field|Type|
  16. +--------------+-----------+
  17. |id|bigint|
  18. |username|field|
  19. |product_name|field|
  20. |summary|field|
  21. |user_id|integer|
  22. |create_time|timestamp|
  23. +--------------+-----------+
  24. 6rowsinset(0.00sec)
  25. mysql>select*frommyorderwherematch('坦克');
  26. +------+--------+------------+-------------+
  27. |id|weight|user_id|create_time|
  28. +------+--------+------------+-------------+
  29. |5|1304|1311895262|1407081600|
  30. |6|1304|1311895263|1406823894|
  31. |7|1304|1311895264|1406823894|
  32. |8|1304|1311895265|1406823894|
  33. +------+--------+------------+-------------+
  34. 4rowsinset(0.00sec)
  35. 0//开源代码phpfensi.com

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

coreseek sphinx 创建表和索引的语句

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