Jump to content


Photo

Is full_text search case sensitive


  • Please log in to reply
7 replies to this topic

#1 neteagle

neteagle
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 18 June 2006 - 03:17 PM

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?

#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 18 June 2006 - 03:45 PM

[!--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]
~ D Kuang

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 June 2006 - 09:51 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 neteagle

neteagle
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 18 June 2006 - 11:58 PM

My collation is UTF8-bin. I want the search to be case-insensitive. How can I do that?


#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 June 2006 - 01:26 PM

[!--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) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 neteagle

neteagle
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 20 June 2006 - 01:40 PM

[!--quoteo(post=386000:date=Jun 20 2006, 08:26 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 20 2006, 08:26 AM) View Post[/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.

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 June 2006 - 05:08 AM

[!--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) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 neteagle

neteagle
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 21 June 2006 - 05:13 AM

[!--quoteo(post=386284:date=Jun 21 2006, 12:08 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 21 2006, 12:08 AM) View Post[/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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users