手把手教你建立私人数据检索库(二)

source sphinx
{
type = mysql
sql_host = 192.168.1.1
sql_user = test  数据源连接信息
sql_pass = test
sql_db = sphinx 数据库名字
sql_port = 3306 数据库库端口号
sql_query_pre = SET NAMES utf8 设置连接字符集
 sql_query = SELECT id,title, post_id, user_id,UNIX_TIMESTAMP(dated) as dateline \
    FROM test  最重要的部分嗷
sql_attr_uint = post_id
sql_attr_uint = user_id
sql_attr_timestamp = dateline 属性字段
}
index sphinx_idx 索引名
{
source = sphinx  这里与上面的source对应
path = usr/local/coreseek-3.2.14/var/data/sphinx 索引要存放的位置
docinfo = extern
charset_type = zh_cn.utf-8
charset_dictpath =/usr/local/coreseek-3.2.14/dict/ 字符集文件存放位置
html_strip = 0
}
indexer
{
    mem_limit = 512M  用来构建索引的索引器运行所占用的内存
}
searchd
{
port = 9351 不要被占用哦netstat-anop|grep 9351  查询服务监听的端口
log = usr/local/coreseek-3.2.14/var/log/search_sphinx.log
query_log = usr/local/coreseek-3.2.14/var/log/query_sphinx.log 相关日志的存放位置
read_timeout = 5
max_children = 30
pid_file = usr/local/coreseek-3.2.14/var/searchd_sphinx.pid
 max_matches = 10000//返回的最大数据量
seamless_rotate = 1
preopen_indexes = 0
unlink_old = 1
}
......

sourcedata1
{
   type                   = mysql
   sql_host               = localhost
   sql_user               = user
   sql_pass               = password
   sql_db                   = sgk
   sql_port               = 3306
   sql_query_pre           = SET NAMES utf8
  sql_query              = SELECT `id`,`username`, `email` FROM data1   #sql_query第一列id需为整数，username、password、email等作为字符串/文本字段，被全文索引
   sql_attr_uint           = id           #从SQL读取到的值必须为整数
   #sql_field_string   = username
   #sql_field_string   = password
   #sql_field_string   = email
   #sql_attr_timestamp       = date_added  #从SQL读取到的值必须为整数，作为时间属性
   sql_query_info           = SELECT `id`, `username`, `password`, `email`, `salt`FROM data1 WHERE id=$id #命令行查询时，从数据库读取原始数据信息
}
#index定义
indexdata1
{
   source           = data1           #对应的source名称
   path           = /usr/local/sphinx/var/data/data1#请修改为实际使用的绝对路径，例如：/usr/local/sphinx/var/...
   docinfo           = extern
   mlock           = 0
   morphology       = none
   min_word_len       = 1
ondisk_dict    = 1   #索引不载入内存而是保存在硬盘上
#这个选项很重要一定要把索引保存在硬盘，否则索引出的海量数据会充满整个内存
   html_strip               = 1
}
#全局index定义
indexer
{
   mem_limit           = 754M
}
#searchd服务定义
searchd
{
   listen                 =   9312
   read_timeout       = 5
   max_children       = 30
   max_matches           = 1000
   seamless_rotate       = 0
   preopen_indexes       = 0
   unlink_old           = 1
   pid_file= /usr/local/sphinx/var/log/searchd_mysql.pid #请修改为实际使用的绝对路径，例如：/usr/local/sphinx/var/...
   log=/usr/local/sphinx/var/log/searchd_mysql.log       #请修改为实际使用的绝对路径，例如：/usr/local/sphinx/var/...
   query_log= /usr/local/sphinx/var/log/query_mysql.log#请修改为实际使用的绝对路径，例如：/usr/local/sphinx/var/...
   binlog_path=                                #关闭binlog日志
}

CREATETABLE IF NOT EXISTS `data1`(
`id` INTUNSIGNED AUTO_INCREMENT,
`email`VARCHAR(255) NOT NULL,
`username`VARCHAR(100) NOT NULL,
`password`VARCHAR(40) NOT NULL,
`salt`VARCHAR(40) NOT NULL,
`from`VARCHAR(40) NOT NULL,
PRIMARYKEY ( `id` )
)ENGINE=MyISAMDEFAULT CHARSET=utf8;

mysqlimport -u root -p --local--fields-terminated-by="   " --lines-terminated-by="\r\n"sgk C:\Users\Administrator\Desktop\data1--columns=username,password,email

mysqlimport -u root -p --local--fields-terminated-by="  ----" --lines-terminated-by="\r\n"sgk C:\Users\Administrator\Desktop\data1--columns=username,password,email