MYSQL 搜索匹配度

有两张表一个是商品表一个是类别表；类别表是一个树形结构的表；
要搜索商品首先根据关键字商品名称匹配度查询；
其次根据关键字模糊查询类别再把类别下面的所有子类别查询出来再查询这些类别下面的商品列表

CREATE TABLE `NewTable` (
`id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '编号' ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '类别名称' ,
`shop_id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '商铺ID' ,
`parent_id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '父级ID' ,
`parent_ids`  varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '所有父级编号' ,
`is_basic`  char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '平台或商家' ,
`is_compatible`  char(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '0' ,
`is_recommend`  char(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '是否推荐' ,
`color`  varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
`create_by`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '创建者' ,
`create_date`  datetime NOT NULL COMMENT '创建时间' ,
`update_by`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '更新者' ,
`update_date`  datetime NOT NULL COMMENT '更新时间' ,
`del_flag`  char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '0' COMMENT '删除标记' ,
`remarks`  varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '备注信息' ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin
COMMENT='类别表'
ROW_FORMAT=COMPACT
;

CREATE TABLE `NewTable2` (
`id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '编号' ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '商品名称' ,
`class_id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '类别ID' ,
`shop_id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '商铺ID' ,
`brand_id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '品牌ID' ,
`images`  varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '图片' ,
`introduction`  varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '简介' ,
`details`  text CHARACTER SET utf8 COLLATE utf8_bin NULL COMMENT '商品详情' ,
`series_id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '系列ID' ,
`is_shelves`  char(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '是否上架' ,
`shelves_date`  datetime NULL DEFAULT NULL COMMENT '上架时间' ,
`audit`  char(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '审核' ,
`is_recommend`  char(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '是否推荐' ,
`sales`  int(11) NULL DEFAULT NULL COMMENT '销量' ,
`sort`  int(11) NULL DEFAULT NULL COMMENT '排序值' ,
`price`  decimal(10,2) NULL DEFAULT NULL COMMENT '价格' ,
`create_by`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '创建者' ,
`create_date`  datetime NOT NULL COMMENT '创建时间' ,
`update_by`  varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '更新者' ,
`update_date`  datetime NOT NULL COMMENT '更新时间' ,
`del_flag`  char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '0' COMMENT '删除标记' ,
`remarks`  varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '备注信息' ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin
COMMENT='商品表'
ROW_FORMAT=COMPACT
;

根据商品名称匹配度查询已经完成那么类别查询如何一条SQL 解决呢

select 
	name ,
	(length(name)-length(replace(name,'iphone','')))/length('iphone') as name_iphone,
	(length(introduction)-length(replace(introduction,'iphone','')))/length('iphone') as introduction_iphone,
	length('iphone')/length(name) as bili,
((length(name)-length(replace(name,'iphone','')))/length('iphone'))* (length('iphone')/length(name)) *1,
((length(introduction)-length(replace(introduction,'iphone','')))/length('iphone'))*0.8
from app_ware where name like '%iphone%' or introduction like '%iphone%' 
order by (((length(name)-length(replace(name,'iphone','')))/length('iphone'))* (length('iphone')/length(name)) *1)+(((length(introduction)-length(replace(introduction,'iphone','')))/length('iphone'))*(length(replace(introduction,'iphone',''))/length(introduction))*0.8) desc , length(name) ASC

wendal

1楼•2490天前

... 这是不是上搜索引擎比较好?

Hamming(tonyyule)

2楼•2490天前

上啥搜索引擎呢

wendal

3楼•2490天前

Lucene

Hamming(tonyyule)

4楼•2490天前

Lucene 对服务器压力大么

wendal

5楼•2490天前

大不大有啥关系呢？

Hamming(tonyyule)

6楼•2490天前

Nutz-book 可以写Lucene嘛

Hamming(tonyyule)

7楼•2470天前

nutzcn Lucene 版本太老了能不能用最新的6.5

Hamming(tonyyule)

8楼•2470天前

换成lucene 6.5 去掉Version.LUCENE_4_9 就可以了

    public List<LuceneSearchResult> search(String keyword, int size)
            throws IOException, ParseException {
        IndexReader reader = luceneIndex.reader();
        try {
            IndexSearcher searcher = new IndexSearcher(reader);
            Analyzer analyzer = luceneIndex.analyzer();
            MultiFieldQueryParser parser = new MultiFieldQueryParser(Version.LUCENE_4_9,
                                                                     fields,
                                                                     analyzer,
                                                                     boosts);
            // 将关键字包装成Query对象
            Query query = parser.parse(keyword);
            TopDocs results = searcher.search(query, size);
            List<LuceneSearchResult> searchResults = new ArrayList<LuceneSearchResult>();
            for (ScoreDoc sd : results.scoreDocs) {
                String id = searcher.doc(sd.doc).get("id");
                searchResults.add(new LuceneSearchResult(id, searcher.doc(sd.doc).get("title")));
            }
            return searchResults;
        }
        finally {
            reader.close();
        }
    }

请先登陆