On Wed, Oct 2, 2013 at 10:22 AM, Matthew Walker mwalker@wikimedia.orgwrote:
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