MySQL case INSENSITIVE by default?

ServiceStack.OrmLite
1

I was very surprised to learn that MYSQL uses a case Insensitive collation sequence by default.

 https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html

From my quick research, SQLite is case sensitive by default.

This created a subtle bug, that only occurred when third party base-64 numbers that looked like a guid but was not a guid.

I’ve found that its possible to fix this using the following

using OrmLite = ServiceStack.DataAnnotations;
…
[OrmLite.CustomField(“varchar(255) CHARACTER SET utf8 COLLATE utf8_bin”)]
string DbField {get;set;}

In the MySql documentation they recement using a Binary() cast

 select * from test_case where binary name  = 'dave';

Documented here.

 https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

My question is: have you factored this into the OrmLite design, or should we be on the lookout for issues if we plan to port to a different database host.

2

The OrmLiteConfig.StripUpperInLike option controls whether to normalize behavior by making LIKE searches case-insensitive, unfortunately this has a performance impact and invalidates indexes so for .NET Core (where performance is more important) this feature is disabled (i.e. OrmLiteConfig.StripUpperInLike=true) and then falls back to use on the native behavior configured in the RDBMS.