我有一个这样的表:
Products ( 'id', 'name', 'description', 'location' )
和搜索字符串:
'car 1000 london'
现在我要做的是:
bring all records where 'car' exists in 'name' or 'description' or 'location'
and
bring all records where '1000' exists in 'name' or 'description' or 'location'
and
bring all records where 'london' exists in 'name' or 'description' or 'location'
我怎么能像这样搜索…
谢谢
这是一个动态查询，它将做你想做的。
declare @search nvarchar(max)
declare @dyn_sql nvarchar(max)
declare @where nvarchar(max)
set @search = 'car 1000 london'
set @search = rtrim(LTRIM(@search))
set @search = REPLACE(@search,' ',',')
set @where = ''
while (LEN(@search) > 0)
begin
declare @place_holder nvarchar(100)
if((select CHARINDEX(',',@search)) = 0)
begin
set @place_holder = @search
end
else
begin
set @place_holder = SUBSTRING(@search, 0, CHARINDEX(',',@search))
end
set @place_holder = REPLACE(@place_holder,',','')
if((select CHARINDEX(',',@search)) = 0)
begin
set @search = ''
end
set @search = SUBSTRING(@search, CHARINDEX(',',@search)+1, LEN(@search))
set @where = @where+'name like ''%'+@place_holder+'%'' or '
set @where = @where+'description like ''%'+@place_holder+'%'' or '
set @where = @where+'location like ''%'+@place_holder+'%'' or '+CHAR(10)
end
set @where = SUBSTRING(@where,0,len(@where)-3)
set @dyn_sql =
'
select
*
from
Products
where
'+@where
exec (@dyn_sql)
In InnoDB
SELECT * FROM products p
WHERE (p.name LIKE '% car %'
OR p.description LIKE '% car %'
OR p.location LIKE '% car %')
UNION
-- same query but with '% 1000 %'
UNION
-- ditto with '% london %'
SELECT
MATCH (p.name, p.location, p.description) AGAINST('car') as relevance,
p.* FROM products p
WHERE MATCH (p.name, p.location, p.description) AGAINST('london')
ORDER BY relevance DESC
UNION
-- same query but with '1000' <<-- see note below
UNION
-- ditto with 'car' <<-- see note below.
Match against的最小长度为5个字符，参见: