Hello all,
I'm running MySQL 5.0.41. I have a table with its charset set to utf8,
collated to utf8_bin. I'm using utf8_bin because I need case-sensitive
unique values in a specific column. I'd like to perform a case-
insensitive fulltext search on this particular column. I've created a
fulltext key, however I cannot seem to change the collation for the
search. I see no docs on the MySQL website either (however I did find
this, which may be useful to some: http://dev.mysql.com/doc/refman/5.0/...-collate.html).
Ultimately, I need inserts to be case-sensitive for uniqueness, but
fulltext searches to be case-insensitive. I've included my test table
creation statement. Any help would be appreciated.

Thanks,
TD

CREATE TABLE TestTable
(
ID int NOT NULL auto_increment,
Chars varchar(100) NOT NULL,
PRIMARY KEY (ID),
UNIQUE (Chars),
FULLTEXT (Chars)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_bin;

tdolfo wrote:
> Hello all,
> I'm running MySQL 5.0.41. I have a table with its charset set to utf8,
> collated to utf8_bin. I'm using utf8_bin because I need case-sensitive
> unique values in a specific column. I'd like to perform a case-
> insensitive fulltext search on this particular column. I've created a
> fulltext key, however I cannot seem to change the collation for the
> search. I see no docs on the MySQL website either (however I did find
> this, which may be useful to some: http://dev.mysql.com/doc/refman/5.0/...-collate.html).
> Ultimately, I need inserts to be case-sensitive for uniqueness, but
> fulltext searches to be case-insensitive. I've included my test table
> creation statement. Any help would be appreciated.
>
> Thanks,
> TD
>
> CREATE TABLE TestTable
> (
> ID int NOT NULL auto_increment,
> Chars varchar(100) NOT NULL,
> PRIMARY KEY (ID),
> UNIQUE (Chars),
> FULLTEXT (Chars)
> ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_bin;
>

This seems to simple to solve the problem but create an index and
searching the database using a case conversion function do the trick?
I've had to do that as you never really can trust users to enter data
properly and as a DBA, you have no control over the code.

Adam

On Jul 9, 1:01 am, Adam Englander <adamenglan...@yahoo.com> wrote:
> tdolfo wrote:
> > Hello all,
> > I'm running MySQL 5.0.41. I have a table with its charset set to utf8,
> > collated to utf8_bin. I'm using utf8_bin because I need case-sensitive
> > unique values in a specific column. I'd like to perform a case-
> > insensitive fulltext search on this particular column. I've created a
> > fulltext key, however I cannot seem to change the collation for the
> > search. I see no docs on the MySQL website either (however I did find
> > this, which may be useful to some:http://dev.mysql.com/doc/refman/5.0/...-collate.html).
> > Ultimately, I need inserts to be case-sensitive for uniqueness, but
> > fulltext searches to be case-insensitive. I've included my test table
> > creation statement. Any help would be appreciated.
>
> > Thanks,
> > TD
>
> > CREATE TABLE TestTable
> > (
> > ID int NOT NULL auto_increment,
> > Chars varchar(100) NOT NULL,
> > PRIMARY KEY (ID),
> > UNIQUE (Chars),
> > FULLTEXT (Chars)
> > ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_bin;
>
> This seems to simple to solve the problem but create an index and
> searching the database using a case conversion function do the trick?
> I've had to do that as you never really can trust users to enter data
> properly and as a DBA, you have no control over the code.
>
> Adam

Adam, thanks for your reply. I thought of doing that, but MySQL
doesn't seem to allow any function modifiers to be used within
MATCH(). Doing this fails: ... WHERE MATCH (UCASE(column_name)) ...

Any other ideas?

Thanks,
TD

tdolfo wrote:
> On Jul 9, 1:01 am, Adam Englander <adamenglan...@yahoo.com> wrote:
>> tdolfo wrote:
>>> Hello all,
>>> I'm running MySQL 5.0.41. I have a table with its charset set to utf8,
>>> collated to utf8_bin. I'm using utf8_bin because I need case-sensitive
>>> unique values in a specific column. I'd like to perform a case-
>>> insensitive fulltext search on this particular column. I've created a
>>> fulltext key, however I cannot seem to change the collation for the
>>> search. I see no docs on the MySQL website either (however I did find
>>> this, which may be useful to some:http://dev.mysql.com/doc/refman/5.0/...-collate.html).
>>> Ultimately, I need inserts to be case-sensitive for uniqueness, but
>>> fulltext searches to be case-insensitive. I've included my test table
>>> creation statement. Any help would be appreciated.
>>> Thanks,
>>> TD
>>> CREATE TABLE TestTable
>>> (
>>> ID int NOT NULL auto_increment,
>>> Chars varchar(100) NOT NULL,
>>> PRIMARY KEY (ID),
>>> UNIQUE (Chars),
>>> FULLTEXT (Chars)
>>> ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_bin;
>> This seems to simple to solve the problem but create an index and
>> searching the database using a case conversion function do the trick?
>> I've had to do that as you never really can trust users to enter data
>> properly and as a DBA, you have no control over the code.
>>
>> Adam
>
> Adam, thanks for your reply. I thought of doing that, but MySQL
> doesn't seem to allow any function modifiers to be used within
> MATCH(). Doing this fails: ... WHERE MATCH (UCASE(column_name)) ...
>
> Any other ideas?
>
> Thanks,
> TD
>
The problem is your collation directive specifies utf8_bin and utf8_bin
does not allow for case insensitive full text searching. You can change
your collation or find another option for searching for your data.

It doesn't look like you're trying to get relevance so why use a full
text search? If you are using a direct match use "WHERE
UCASE(column_name) = UCASE('value')" or if your searching within the
string use "WHERE UCASE(column_name) like UCASE('%value%')".

Unfortunately, it appears that MySQL doesn't allow for function based
indexes like UCASE(column_name), I do miss Oracle sometimes, so you
won't be able to use an index.

If your table is large enough to really need an index or you really need
full text searching, you could create an insert trigger with MySQL 5.x
to store an upper case version of your column in a different column.

Adam