MySQL search with uft8_general_ci is case sensitive for FULLTEXT?

Question

I set up a MyISAM table to do FULLTEXT searching. I do not want searches to be case-sensitive.

My searches are along the lines of:

SELECT * FROM search WHERE MATCH (keywords) AGAINST ('+diversity +kitten' IN BOOLEAN MODE);

Let's say the keywords field I'm looking for has the value "my Diversity kitten".

I noticed the searches were case-sensitive. I double-checked my collation on the search table, it was set to utf8_bin. D'oh! I changed it to utf8_general_ci.

But my query is still case-sensitive! Why?

Is there a server setting I need to change, too?

Is there something I need to do besides change the collation?

I did a "REPAIR TABLE search QUICK" to rebuild the FULLTEXT index, but that didn't do it either...

My searches are still case-sensitive. =(

The table may be utf8_general_ci, but are the columns?

Piskvor left the building
– Piskvor left the building

2011-02-09 13:26:37 +00:00
Commented Feb 9, 2011 at 13:26 — Piskvor left the building
– Piskvor left the building, Commented Feb 9, 2011 at 13:26

Tev · Accepted Answer · 2011-02-09 14:54:31Z

6

Aha, figured it out for reals this time.

I believe my issue was using NaviCat to update the collation. I have an older version of NaviCat, maybe it was a bug or something.

Doing:

ALTER TABLE search CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

fixed it correctly.

Always use command line, kids! =)

answered Feb 9, 2011 at 14:54

Tev

6901 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stefan Gehrig · Accepted Answer · 2011-02-09 13:21:50Z

1

Hmm - that behavior doesn't match the manual:

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.

Which version of MySQL do you use? Can you provide some data that would allow replicating the problem on another machine?

answered Feb 9, 2011 at 13:21

Stefan Gehrig

83.9k24 gold badges162 silver badges193 bronze badges

Collectives™ on Stack Overflow

MySQL search with uft8_general_ci is case sensitive for FULLTEXT?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related