MySQL Forums
Forum List  »  Full-Text Search

Re: match against fails on mixed case
Posted by: Phil Petree
Date: February 08, 2013 03:06PM

I dont post actual tables, I modify working tables to illustrate the problem. I cleaned up the examples I posted, try these...

CREATE TABLE IF NOT EXISTS `test1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`bucket_id` int(10) unsigned NOT NULL COMMENT 'folder this component belongs to',
`test1_name` varchar(81) NOT NULL COMMENT 'Name of this component',
`test1_desc` varchar(1024) NOT NULL COMMENT 'Component Description',
PRIMARY KEY (`id`),
FULLTEXT KEY `test1_search` (`test1_name`,`test1_desc`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci, AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `bucket` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`isvisible` int(1) unsigned,
`bkt_name` varchar(81) NOT NULL COMMENT 'The name of this bucket',
`bkt_desc` varchar(1024) NOT NULL COMMENT 'A description of this bucket',
`bkt_keywords` varchar(512) DEFAULT NULL COMMENT 'keywords for searches',
PRIMARY KEY (`id`),
FULLTEXT KEY `bkt_search` (`bkt_desc`,`bkt_keywords`,`bkt_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci, AUTO_INCREMENT=1;

INSERT INTO `bucket` (`id`, `bkt_name`, `bkt_desc`, `bkt_keywords`) VALUES
(1, 1, 'Simpsons', 'The Simpsons Cartoon Family was first successful adult cartoon series', 'Homer, Marge, Lisa and Bart'),
(2, 1, 'Griffins', 'The family from the popular family guy series', 'Peter, Lois, Meg, Chris, Stewie, Brian');

INSERT INTO `test1` (`id`, `bucket_id`, `test1_name`, `test2_desc`) VALUES
(1, 1, 'Homer Simpson', 'Homer the figurative head of the Simpsons Family and is the husband of Marge'),
(2, 2, 'Peter Griffin', 'Peter the figurative head of the Griffin family on the hit TV series The family Guy');

SELECT *
FROM bucket
JOIN test1 ON test1.bucket_id = bucket.id
WHERE
bucket.isvisible > 0 AND
MATCH(bucket.bkt_keywords, bucket.bkt_desc, bucket.bkt_name)
AGAINST('family' IN BOOLEAN MODE) OR
MATCH(test1.test1_name, test1.test1_desc)
AGAINST('family' IN BOOLEAN MODE)

Options: ReplyQuote


Subject
Views
Written By
Posted
4821
February 07, 2013 10:43AM
2347
February 08, 2013 10:03AM
Re: match against fails on mixed case
2358
February 08, 2013 03:06PM
2535
February 14, 2013 03:50PM
2317
February 20, 2013 04:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.