Case sensitivity/insensitivity in MySQL queries?

For discussion related to MythTV which doesn't belong in another forum.

Moderator: Forum Moderators

Post Reply
wmorrison
Junior
Posts: 31
Joined: Sat Dec 01, 2018 12:05 am
United States of America

Case sensitivity/insensitivity in MySQL queries?

Post by wmorrison » Fri Oct 11, 2019 12:13 pm

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.

User avatar
bill6502
Developer
Posts: 1358
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Case sensitivity/insensitivity in MySQL queries?

Post by bill6502 » Fri Oct 11, 2019 4:00 pm

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

Gribnif
Junior
Posts: 55
Joined: Wed May 14, 2014 12:46 pm
United States of America

Re: Case sensitivity/insensitivity in MySQL queries?

Post by Gribnif » Fri Oct 11, 2019 8:34 pm

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".

Post Reply