Блог

У меня есть такая таблица:

 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)
  

В 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 %'
  

В MyISAM

   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 символов, см.:

http://dev.mysql.com/doc/refman/5.5/en/fulltext-restrictions.html