SqlAlchemy does not force case-sensitive matching for MySQL (or any other) DBs

Linked on Mar 24 at 18:11

MySQL defaults to case-insensitive string comparison. SqlAlchemy does not do anything to mask this behaviour, and rightly so. This SqlAlchemy bug contains the reasoning as well as links to articles on how to configure MySQL tables and columns to use case-sensitive comparisons.

When defining a column or a table in MySQL, you can specify the charset as utf8. By default, the collation will be utf8_general_ci, which is case-insensitive. Specifying the collation as utf8_general_cs will result in case-sensitive comparisons.

You can also precede the MySQL column name with “BINARY” in a WHERE or LIKE clause. This will force the column to act as a binary column, thus inducing case-sensitive comparisons. The COLLATE clause in a SELECT can also be used to override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements.

Leave a Reply