1

Is there any way to specify COLLATE and MATCH() AGAINST() in the same query string. I'm trying to get this.

SELECT * FROM table  WHERE COLLATE latin1_general_cs MATCH(column) AGAINST('string')

I know that won't work, just an example. If COLLATE dose not work with MATCH. How can I specify the case sensitivity in every query, because I need both case sensitive and case insensitive MATCH() AGAINST() queries.

Robby Cornelissen
  • 91,784
  • 22
  • 134
  • 156
X-27 is done with the network
  • 292
  • 7
  • 21

2 Answers2

0

I figured out how to do this. Like @Ollie Jones said, I copied all data it to a new column,with case sensitive collation. In my case latin1_general_cs.

But because FULLTEXT indexes can NOT have mixed collation. There must be 2 FULLTEXT indexes. One on the case insensitive column (latin1_general_ci)and one on the case sensitive column (latin1_general_cs).

X-27 is done with the network
  • 292
  • 7
  • 21
-1

No, unfortunately

Although the use of multiple character sets within a single table is supported, all columns in a FULLTEXT index must use the same character set and collation.

There's no modifier on the full text MATCH() AGAINST syntax to specify collation.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html

You could maintain a copy of the column column with a different collation.

O. Jones
  • 103,626
  • 17
  • 118
  • 172