Re: [Refdb-users] Case sensitivity

[Markus H. <mar...@mh...>]

Hi,

Z F writes:
 > This is what I thought. MySQL does the right thing when using
 > = and ~ on the text fields. As far as I understood, author name and
 > title are text fields. Am I wrong? If yes, the comparison should work
 > correctly. However, comparisons on the author field work in
 > case-insensitive way, while comparisons on the title field are case
 > sensitive. So this suggests that author field is text, while title
 > field is somethign else. Is there logic behind this? According to 
 > documentation both fields are alphanumeric.
 > 

I wouldn't call it "logic", but there is a historical reason. The
author field (and keyword, periodical, and a couple of others) are
fixed-length fields which are stored in VARCHAR columns, as this is
the most economical type in this case. Title, notes, abstract and other
fields of unlimited length are stored as binary strings. I chose
binary over text back then because I *wanted* the search in these
fields to be case-sensitive.

However, with MySQL 4.1 and later binary strings actually have a
disadvantage as they don't use the character encoding when it comes to
the sorting order. Using the proper character encoding may actually
outweigh case-sensitive search (which you don't seem to like anyway)
these days. I'm open for a discussion how this should be treated in
the future. In any case, MySQL is quite a mess in these issues.

regards,
Markus

-- 
Markus Hoenicka
mar...@ca...
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de