List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:August 4 1999 7:59pm
Subject:Re: case-sensitivity + small "bug"
View as plain text  
Hi Luke

Luke Meyer wrote:
> Hopefully I'm not rehashing old issues here...
> The default for string comparison in Mysql is case-insensitive -- a nice
> feature, but how do you turn it off?  :-)  I'd like to have a query like
> SELECT Thingie FROM Thing WHERE Kind='a';
> only actually select Things with a lowercase 'a' as Kind, not uppercase 'A'.
> Can someone please help?

You could make the column BINARY.
Then all comparisons will be case-sensitive.

If you upgrade to 3.23.xx, then you can use an per expression cast on CHAR, VARCHAR or
*TEXT like this:
SELECT Thingie FROM Thing WHERE BINARY Kind = 'a';

> Also, there is something of a "bug" (more like a user error, depends how
> you look at it) at least in version 3.22.22 which I'm using for my website.
> If you have a table (call it Thing again) with an auto-increment column
> (call it ThingID) and you set one of your ThingID's to 0, then you can't
> alter that table afterwards.  You get something like this:
> ALTER TABLE Thing add foo int;
> ERROR 1062: Duplicate entry '41' for key 1
> I'm guessing this is because of how Mysql alters tables -- reconstructs the
> whole thing and when it tries to insert the value with 0, gets something
> bogus back.  So there might be other similar problems with, say, writing the
> table to a file and then re-reading it -- I haven't checked.

The problem here is, that 0 (= zero) is also used as AUTO_INCREMENT value like NULL.
There are  ODBC reasons for this.

> Lest you wonder why anyone would want to do this... my website was designed
> with a specification that the special administrative account would have ID 0,
> and I had the whole thing implemented and running live before I realized that
> I couldn't add more columns to my account table.  I have to delete the
> admin account first to do so.  So, maybe this might be worth someone's time
> to fix.  For all I know, it's already been fixed in a more recent version, in
> which case, sorry for wasting bandwidth :-)

If your AUTO_INCREMENT field is not defined as UNSIGNED, then I just would change the zero
values to -1 bevor doing the ALTER TABLE.

> Luke


case-sensitivity + small "bug"Luke Meyer4 Aug
  • Re: case-sensitivity + small "bug"FRed4 Aug
  • Re: case-sensitivity + small "bug"Christian Mack4 Aug
  • Re: case-sensitivity + small "bug"Martin Ramsch5 Aug