Search code examples
mysqlselectinnodbsql-likecase-sensitive

Like search is case sensitive?

I have a query like this, executed on a table where there are a couple keywords comma seperated in the field keywords with a leading and trailing comma:

SELECT media_id, filename FROM media WHERE keywords LIKE '%,house,%'

However, it won't find anything (there are a couple thousand records in that table, quite a bunch of which have the keyword "House"). Even searching for '%,House,%' does find nothing.

What works is doing the like search in all caps:

SELECT media_id, filename FROM media WHERE keywords LIKE '%,HOUSE,%'

Now it will also find keywords like House.

Collation of the table and the keywords column is latin1_german1_ci.

Where does this behaviour come from?
How do I get the usual completely case-insensitive behaviour?

Solution

  • Extremely stupid and unsolvable problem.

    I expanded keywords like or or and to actual query logic even if they were within a word in the preprocessing script, so searching for handsome would be expanded to h && some.

    I'm sorry.