- UUID's as Primary Keys in MySQL
- USING UUID AS PRIMARY KEY IN MYSQL/MARIADB DATABASES
- Which UUID version to use?
In this example, we will use Laravel UUID package to create UUID version 4 and store it as BINARY(16) in MySQL database.
The Accessor and Mutator will be used in an Eloquent model named Post
to streamline the usage.
This can be done via composer
composer require "webpatser/laravel-uuid=1.*"
The binary
type is mapped to BLOB
type of MySQL, so we will have to use DB::statement()
to create a column of type BINARY(16) inside the migration instead.
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class AddUuidColumnToPostsTable extends Migration {
public function up()
{
DB::statement('ALTER TABLE posts ADD uuid BINARY(16) NOT NULL AFTER id;');
DB::statement('CREATE UNIQUE INDEX posts_uuid_unique ON posts (uuid);');
}
public function down()
{
DB::statement('DROP INDEX posts_uuid_unique ON posts;');
DB::statement('ALTER TABLE posts DROP uuid;');
}
}
Note that a migration for table posts
should be created normally without uuid
column. The above migration will modify the table to add uuid
instead.
Create the Post
model manually or via artisan
command, then add the Accessor and Mutator for UUID column.
<?php
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
protected $guarded = ['id];
public function setUuidAttribute($value)
{
$this->attributes['uuid'] = hex2bin(str_replace('-', '', $value));
}
public function getUuidAttribute($value)
{
return bin2hex($value);
}
}
When querying the table that contains BINARY(16) of UUID, there might be a problem converting BINARY(16) value to string.
In that case, use DB::raw('HEX(posts.uuid) as uuid')
to first convert the BINARY(16) to string.
$query = DB::table('posts')
->select('posts.id', DB::raw('HEX(posts.uuid) as uuid'), 'posts.date', 'posts.body')
->get();
When I used Binary UUID with eager loading relationship, it always return null. Do you have any idea to resolve this issue?