I would like to use base62 unique identifiers and my problem is that the columns are not case sensitive, so F1 is the same as f1 when I search for it. Now in MYSQL I would simply do
CREATE TABLE USERS
(
USER_NAME STRING(10) BINARY
)
So in Laravel it should look like
$table->string('base62_id', 10)->binary();
However, I don't think ->binary() exists in laravel for this purpose. So how would I do that?
I understand this question is old, but in case anyone stumbles upon it. At time of writing, Laravel is version 8 and this is valid:
$table->string("case_sensitive_id")->charset("utf8")->collation("utf8_bin")->nullable();
This will achieve case sensitivity without any sort of alter statements.
So this is the answer:
DB::statement("ALTER TABLE `mytable` ADD `base62_id` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_bin UNIQUE AFTER `id` , ADD INDEX ( `base62_id` )");
The key is to use
CHARACTER SET utf8 COLLATE utf8_bin
to make it case sensitive.
Thank you @ my source: http://blog.birdhouse.org/2010/10/24/base62-urls-django/comment-page-1/
DB::statement("ALTER TABLE yourtable ADD binary(10) AFTER id");. You're using MySQL, there's no need to be afraid of MySQL specific DDL. Laravel'sbinaryis MySQL'sBLOB, which is a variable-length column instead of fixed-length column.binarymeans there's no character set, hence there's no case sensitivity. You save bytes, not strings.f1orF1is a hexadecimal number represented as something you can read. There's no notion of uppercase F or lowercase f. If you're going to save something to a binary column, that should be binary data, an example beingUNHEX(SHA1('test'));. If you don't need binary, and apparently you don't, why don't you simply use achar?BLOB(that's fine). You want abinary. I show you how to add abinarycolumn, but you say that's not what you want (weird, but ok, that was exactly what you asked for). I suggest that you use char, but you have case sensitivity issue so you need a binary one (which is just fine). What exactly do you want then? I literally gave you the SQL that you copypaste into your migration to get a binary column which is 10 chars long, but hey, that's not what you want, you want case-sensitive 10 char long column. I'm confused right now to say the least:)