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;