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
Tschau
Christian
