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

you could do it with DB::statement('CREATE TABLE t (url_hash BINARY(16))'); — Sari Yono
– Sari Yono, Commented Mar 20, 2018 at 17:08
@SariYono : Will that not create a double table? aka an error? But thanks , I think it is getting me on the right track. — Paolo_Mulder
– Paolo_Mulder, Commented Mar 20, 2018 at 17:34

mpskovvang · Accepted Answer · 2020-06-27 21:32:48Z

17

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

answered Jun 27, 2020 at 21:32

mpskovvang

2,3332 gold badges18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mikkpokk Over a year ago

Whoever lands here, I can confirm, it really creates correct binary column in MySQL. $table->char('url_hash', 16)->charset('binary'); Results: CREATE TABLE test (url_hash binary(16) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Jonas Staudenmeir · Accepted Answer · 2018-03-21 00:08:57Z

10

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);
});

answered Mar 21, 2018 at 0:08

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

1 Comment

Tiago Peres Over a year ago

Would you still say the same today?

Ben · Accepted Answer · 2018-03-20 23:32:19Z

4

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');

answered Mar 20, 2018 at 23:32

Ben

5,1494 gold badges20 silver badges26 bronze badges

Collectives™ on Stack Overflow

Laravel Schema Builder : Creating a binary(16) column

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related