On Wed, Oct 2, 2013 at 10:22 AM, Matthew Walker <mwalker(a)wikimedia.org>wrote;wrote:
Given that I want to support case insensitive searching; does anyone have
any thoughts or examples on how to go about doing it in a binary table?
The only solution I can think of would be to change the collation/charset
of the table in question to utf8.
For a single search field there is no need to change the whole table
charset or collation. Simply make the field a character type (VARCHAR /
CHAR / TEXT), utf8 if that's suitable, and choose a case-insenitive
collation. Eg:
ALTER TABLE <tbl> MODIFY <col> VARCHAR(N)
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
More info on string comparison:
http://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html
However I'd like to hear the historical reasons why MW does everything
binary too. There certainly were some character set issues in older MySQL
versions and afaik we still have to support MySQL 5.0.2 and up.
BR
Sean