Page 1 of 1

Case sensitivity/insensitivity in MySQL queries?

Posted: Fri Oct 11, 2019 12:13 pm
by wmorrison
I've noticed some queries are automatically case-insensitive and others are not.

For instance, program.category. If you're querying for 'Comedy', it will also match if your query string uses 'comedy'.

But people need capitalization in their names. people.name='samuel l. jackson' will find no results, while 'Samuel L. Jackson' will.

This is even when I use MySQL Workbench to run queries, so it's not MythTV code that's controlling the case sensitivity.

Is this something configured in MySQL? Is it a per-table setting? Per column? I know SQL but am new to MySQL.

Re: Case sensitivity/insensitivity in MySQL queries?

Posted: Fri Oct 11, 2019 4:00 pm
by bill6502
Use: SHOW FULL COLUMNS FROM people; and then FROM program. Note the Collation column
for the columns of interest. It's set at DB creation time in the MythTV product. There are only six
columns in the DB that use utf8_bin.

Note that there are Python and Perl bindings as well as the Services API that can be (should be)
used to access the DB.

API Example: https://www.mythtv.org/wiki/Guide_Servi ... ogramGuide

Re: Case sensitivity/insensitivity in MySQL queries?

Posted: Fri Oct 11, 2019 8:34 pm
by Gribnif
To expand on what bill6502 said, if you want a particular column to be searched in a case-insensitive way, change it to a case-insensitive collation. These end in "_ci", like "latin1_general_ci" and "utf8_general_ci".