Case sensitivity in MySQL
By default MySQL is not case sensitive when performing comparisons against string columns unless you explicitly declare them as BINARY in the table schema. However, I was caught out when performing a query similar to the following (prepared statement):
SELECT
label, composer, workTitle, worksId
FROM
labels L INNER JOIN
composers C ON (C.id = L.composerId) INNER JOIN
works W ON (W.id = C.worksId)
WHERE
CONCAT(
label,' ',composer,' ',workTitle,' ',wordsId
) AS search LIKE '%?%'
LIMIT 20;
Basically it’s a bit of hack to perform a search on all search terms (for single term searches). However, when I ran this query with data I knew existed in the table, my lowercase term returned no results. So I exactly matched the term, and got rows back.
Comparisons on calculated criteria using string functions would appear to be case sensitive. Simply lowering the case of both the search field and my LIKE keyword(s) fixed the issue.
SELECT
label, composer, workTitle, worksId
FROM
labels L INNER JOIN
composers C ON (C.id = L.composerId) INNER JOIN
works W ON (W.id = C.worksId)
WHERE
CONCAT(
LOWER(label),' ',LOWER(composer),' ',LOWER(workTitle),' ',wordsId
) AS search LIKE '%?%'
LIMIT 20;


