Laravel Schema Builder : Creating a binary(16) column

Question

Using Laravel 5.5 and Mysql (10.1.19-MariaDB)

For a md5 hash I want a binary(16) column. Let's call the colum url_hash

When using :

$table->binary('url_hash');

it will give me a BLOB column.

source : https://laravel.com/docs/5.5/migrations#creating-columns

I have seen all kind of hacks or plugins around the web for this , but what is the most simple one without any external plugins that could break on the next update?

Cheers

Answer 1

Extend the MySqlGrammar class, e.g. in app/MySqlGrammar.php:

namespace App;

use Illuminate\Support\Fluent;

class MySqlGrammar extends \Illuminate\Database\Schema\Grammars\MySqlGrammar {

    protected function typeRealBinary(Fluent $column) {
        return "binary({$column->length})";
    }

}

Then use a macro to add your own column type:

DB::connection()->setSchemaGrammar(new \App\MySqlGrammar());

Blueprint::macro('realBinary', function($column, $length) {
    return $this->addColumn('realBinary', $column, compact('length'));
});

Schema::create('table', function(Blueprint $table) {
    $table->realBinary('url_hash', 16);
});

Answer 2

You can just set the character set to binary.

$table->char('url_hash', 16)->charset('binary');

This is actually shown as a real binary column type with a length of 16 in MySQL Workbench.

There shouldn't be any difference: https://stackoverflow.com/a/15335682/5412658

Answer 3

Laravel author recommends to do a DB:statement call and run the raw SQL.

If you are running migration, you could run this raw SQL after Schema::create:

DB::statement('ALTER TABLE table_name ADD url_hash binary(16) AFTER some_column');

Depends on use case, you could need to run this raw SQL to drop the column before dropping the table:

DB::statement('ALTER TABLE table_name DROP url_hash');