Case Sensitive WHERE Clause
Posted on 12 September 2018 in development
Today I found a bug in my URL shortener app;
It didn’t occur to me that the fields in a table aren’t automatically case sensitive. This is a problem for my app because the auto-generated URLs uses base62 (A-Za-z0-9). “a1b2” will forward to the same link as “A1B1”.
I learned that this is the expected behaviour for Laravel’s default database collation. “UTF8_unicode_ci” isn’t case sensitive. To change that, I can use “UTF8_bin” for the whole database. But that introduces another peculiarity to my database when it comes to ORDER BY.
So in this case, I prefer to keep the default charset/collation and just use “UTF8_bin” for one specific table.
To fix this behaviour. I’ve added these two lines to my migration:
$table->charset = 'utf8'; $table->collation = 'utf8_bin';
And now everything works as intended.
Alternatively, if you’re using MySQL version 8+, you only need the following line:
$table->collation = 'utf8mb4_0900_as_cs'