使用mysql如何计算成功OR的数量？

Question

I basically want to count the number of ors that are successful so that I can tell the relevance of each item to the search query. This is the table structure:

CREATE TABLE `items` (
 `id` int(11) NOT NULL auto_increment,
 `listing_id` int(11) NOT NULL,
 `name` varchar(256) NOT NULL,
 `description` varchar(1000) NOT NULL,
 `image` varchar(100) NOT NULL,
 `image_caption` varchar(100) NOT NULL,
 `keywords` varchar(10000) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=90 DEFAULT CHARSET=latin1

and this is an example of a row:

 `id` = 1
 `listing_id` = 1,
 `name` = 'listing test',
 `description` = 'listing desc',
 `image` = '',
 `image_caption` = '',
 `keywords` = 'youtube video fun'

And this is what I want the mysql to do in pseudo code:

 SELECT id,name,description AND count FROM items WHERE `keywords` LIKE '%video%' AND LIKE '%keyword2%' AND LIKE '%youtube%' AND LIKE '%text%'

And I want to get these results:

 `id` = 1
 `name` = 'listing test',
 `description` = 'listing desc',
 `count` = 2

Answer 1

Something like this. (I used "score" instead of "count" because "count" is a SQL function name.)

SELECT id, name, description, (
    IF(keywords LIKE '%video%', 1, 0)
  + IF(keywords LIKE '%keyword2%', 1, 0)
  + IF(keywords LIKE '%keyword3%', 1, 0)
) score
FROM table
HAVING score > 0

Note that this query will be quite slow, as it must examine every row.