MySQLdb compare lower

[Chris Angelico]

On Sat, Dec 15, 2012 at 8:49 AM, Dennis Lee Bieber
<wlfraed at ix.netcom.com> wrote:
> On Fri, 14 Dec 2012 15:16:54 +1100, Chris Angelico <rosuav at gmail.com>
> declaimed the following in gmane.comp.python.general:
>
>> Yeah, it's one of the things that tripped me up when I did a
>> MySQL->PostgreSQL conversion earlier this year. The code was assuming
>> case insensitivity, and began failing on PG. Fortunately the simple
>> change of LIKE to ILIKE solved that.
>>
>> I'd MUCH rather be explicit about wanting case insensitivity. I wonder
>> who would go insane first if I ran an app using Wine under Linux in a
>> directory that's mounted via cifs from a Windows or OS/2 box... (And
>> yes, I have that setup, ready to go. Only there's an extra level of
>> indirection - I'd run it in an sshfs mount that points to my proxy
>> box. Extra safety, keeps messy networking away from my real boxes.)
>
>         I suspect it is a 50/50 proposition. Having case-insensitive
> compares is probably easier for the simpler uses of MySQL (the many web
> servers configured with MySQL, provided as a convenience for untrained
> customers)... They don't have to tell users (on web page input forms)
> that search terms are case sensitive, and they don't have to come up
> with some code for a means of "logical" casing of input (for example,
> searching for a title, where it is common for words like "of", "a",
> "and" to be lowercase, but everything else is capitalized).

I still don't need to tell users that search terms are case sensitive. Look:

http://rosuav.com/1/

Type in a search term, it's not case sensitive! Oh, that's because it
uses ILIKE and everything happens correctly. Hmm. I wonder how I'd
change that (which is backed by PG) to be case sensitive? Ah! I make
it use LIKE instead. How do I make MySQL case sensitive? Declare the
column as binary, or as text, but with a different collation. And that
affects sorting too. Can you guarantee that you can, for instance,
have a column sorted case insensitively and searched case sensitively?
It's probably possible, but I wouldn't depend on it without searching
the docs - or, more likely, browsing StackOverflow answers.

Database defaults are all very well, but it's better to have a safe
default that's easily overridden than an enforced state that you have
to change configuration to get around. Explicit is better than
implicit.

ChrisA