nutz mysql 关键词_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