update column collation with laravel

Question

I have a case-insensitive collation column in my table.

col_name : hash_id, collation : utf8mb4_unicode_ci

I am getting results for yA2JeGs and YA2JeGs when I search for former only.

So I need to update the collation to ensure case-sensitivity for that column.

I tried changing the collation for that column creating a new migration file:

public function up()
{
    Schema::table('product_match_unmatches', function (Blueprint $table) {
        $table->string('hash_id')->collate('utf8mb4_bin')->change();
    });
}

Also with $table->string('hash_id')->collation('utf8mb4_bin')->change();

Migration runs successfully but the collation remains the same.

How do I do that in laravel?

Answer 1

You need to create new migration and make column case sensitive using laravel schema builder with code mentioned below :

$table->string('columName')->collation('utf8_bin')->change();

https://laravel.com/docs/7.x/migrations

Answer 2

I can only change the collation with a raw query, the Blueprint change() method did not do a thing for me, unless I changed the field type too.

This worked for me:

public function up()
{
    DB::statement("ALTER TABLE tableName MODIFY columnName VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
}

Answer 3

You can convert the collation at query time

Model::where(\DB::raw('hash_id COLLATE utf8mb4_bin'), 'yA2JeGs');

However you should probably change the collation of the table itself since it seems that a case insensitive collation is the wrong collation to have used on that field to begin with.

Note: MySQL does not have a utf8mb4_general_cs collation. However if you are using only ASCII hash codes then utf8mb4_bin should be good enough.

Answer 4

Direct Collation is not working in the blueprint need to add charset.

This worked for me :

$table->text('message')->charset('utf8mb4')->collation('utf8mb4_general_ci')->nullable()->change();