neteagle Posted June 18, 2006 Share Posted June 18, 2006 I want to do a full_text search query.SELECT * FROM `product` WHERE MATCH (`product_name`) AGAINST ('Flower')With this query, I got some results,but when I changed 'Flower' to 'flower', I got nothing.It seems full_text search function case sensitive.How can I fix it? Quote Link to comment Share on other sites More sharing options...
poirot Posted June 18, 2006 Share Posted June 18, 2006 [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]By default, the search is performed in case-insensitive fashion. However, you can perform a case-sensitive full-text search by using a binary collation for the indexed columns. For example, a column that uses the latin1 character set of can be assigned a collation of latin1_bin to make it case sensitive for full-text searches.[/quote][a href=\"http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html[/a] Quote Link to comment Share on other sites More sharing options...
fenway Posted June 18, 2006 Share Posted June 18, 2006 Actually, this has nothing to do with fulltext searching per se, but rather how MySQL treats individuals columns for all types of comparisons; it is ultimately dependent on the collation. Quote Link to comment Share on other sites More sharing options...
neteagle Posted June 18, 2006 Author Share Posted June 18, 2006 My collation is UTF8-bin. I want the search to be case-insensitive. How can I do that? Quote Link to comment Share on other sites More sharing options...
fenway Posted June 20, 2006 Share Posted June 20, 2006 [!--quoteo(post=385476:date=Jun 18 2006, 07:58 PM:name=Net Eagle)--][div class=\'quotetop\']QUOTE(Net Eagle @ Jun 18 2006, 07:58 PM) [snapback]385476[/snapback][/div][div class=\'quotemain\'][!--quotec--]My collation is UTF8-bin. I want the search to be case-insensitive. How can I do that?[/quote]I'm fairly sure that you can reset the collation use the COLLATE attribute after the field, but I don't remember the syntax specifically; I'm sure it's in the refman pages. Quote Link to comment Share on other sites More sharing options...
neteagle Posted June 20, 2006 Author Share Posted June 20, 2006 [!--quoteo(post=386000:date=Jun 20 2006, 08:26 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 20 2006, 08:26 AM) [snapback]386000[/snapback][/div][div class=\'quotemain\'][!--quotec--]I'm fairly sure that you can reset the collation use the COLLATE attribute after the field, but I don't remember the syntax specifically; I'm sure it's in the refman pages.[/quote]Thanks fenway, I know it can reset the collation, I just want to know how to make the full_text search function case-insensitive with UTF8. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 21, 2006 Share Posted June 21, 2006 [!--quoteo(post=386008:date=Jun 20 2006, 09:40 AM:name=Net Eagle)--][div class=\'quotetop\']QUOTE(Net Eagle @ Jun 20 2006, 09:40 AM) [snapback]386008[/snapback][/div][div class=\'quotemain\'][!--quotec--]Thanks fenway, I know it can reset the collation, I just want to know how to make the full_text search function case-insensitive with UTF8.[/quote]I didn't mean reset -- I said that you can change it on-the-fly with the collate attribute to "utf8_general_ci"... see [a href=\"http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html\" target=\"_blank\"]here[/a]. Sorry about the mix-up. Quote Link to comment Share on other sites More sharing options...
neteagle Posted June 21, 2006 Author Share Posted June 21, 2006 [!--quoteo(post=386284:date=Jun 21 2006, 12:08 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 21 2006, 12:08 AM) [snapback]386284[/snapback][/div][div class=\'quotemain\'][!--quotec--]I didn't mean reset -- I said that you can change it on-the-fly with the collate attribute to "utf8_general_ci"... see [a href=\"http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html\" target=\"_blank\"]here[/a]. Sorry about the mix-up.[/quote]Thanks fenway, I got it Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.