MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:August 5 2004 1:44pm
Subject:Re: Unique Key question
View as plain text  
"Paul McNeil" <paulie@stripped> wrote on 05/08/2004 13:20:03:

> Good morning.
> I have a table with field name DATA type tinytext.  The table is already
> populated.  I need DATA to be unique.  It seems that MySQL doesn't look 
at
> the uniqueness of a field in a binary fashion.
> 
> Example (chosen because I think it is just plain odd)
> june = Júne
> 
> If I query as -
> Select * from myTable where DATA = 'june' - I am returned both.
> 
> If I query as
> Select * from myTable where cast(DATA as binary) = 'june' - I am 
returned
> only one.
> 
> How can I set this table so that the unique key is based on the binary 
value
> of the field?  Do I have to alter the field type or is there a better 
way?

MySQL explicitly regards field of the various CHAR types as case 
independent. This iw ahet people more commpny want - June and june are, to 
most people, effectively the same month. If you want case-dependent 
access, you should make the columns of BLOB type. Since this is the only 
difference between CHAR and BLOB types if you are not using FULLTEXT 
indexes, it is difficult to see that there is a "better" way. Obviously, 
if you sometimes want one and sometimes the other, you will inevitably 
have to do a cast.

In 4.1, you may also be able to achieve the same effect by playing tricks 
will collations, but this does not seem to me a better way.

        Alec

Thread
Unique Key questionPaul McNeil5 Aug
  • Re: Unique Key questionAlec.Cawley5 Aug
  • Re: Unique Key questionmos5 Aug
    • Re: Unique Key questionMichael Stassen5 Aug