PHP
MySQL
laravel

Laravel+mysqlでグローバルに一意のIDを生成するメモ

mysqlのREPLACE構文を使用してデータベース内の1つの行をアトミックに更新し、新しい自動インクリメンタルプライマリIDを取得することができます。

REPLACE は、INSERT とまったく同じように機能します。ただし、テーブル内の古い行に、PRIMARY KEY または UNIQUE インデックスに関して新しい行と同じ値が含まれている場合、その古い行は新しい行が挿入される前に削除されます。セクション13.2.5「INSERT 構文」を参照してください。

DBを作成

$ php artisan make:migration create_user_sequences_table

Created Migration: 2018_09_14_120348_create_user_sequences_table

upメソッドを変更

2018_09_14_120348_create_user_sequences_table.php
public function up()
{
    Schema::create('user_seqs', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->char('stub', 1)->default('');
        $table->unique('stub');
    });
}
$ php artisan migrate:fresh

laravelにREPLACE構文を追加する

Illuminate\Database\Connectionを継承してIlluminate\Database\Query\Builderを差し替える

Connection.php
namespace App\Library\Database;

use App\Library\Database\Query\Builder;
use Illuminate\Database\Connection as BaseConnection;

class Connection extends BaseConnection
{
    /**
     * Get a new query builder instance.
     *
     * @return Builder
     */
    public function query()
    {
        return new Builder(
            $this, $this->getQueryGrammar(), $this->getPostProcessor()
        );
    }
}
Builder.php
namespace App\Library\Database\Query;

use Illuminate\Database\Query\Builder as QueryBuilder;

class Builder extends QueryBuilder
{
    /**
     * Insert a new record into the database.
     *
     * @param  array  $values
     * @return bool
     */
    public function replace(array $values)
    {
        if (empty($values)) {
            return true;
        }

        if (! is_array(reset($values))) {
            $values = [$values];
        } else {
            foreach ($values as $key => $value) {
                ksort($value);
                $values[$key] = $value;
            }
        }

        $bindings = [];

        foreach ($values as $record) {
            foreach ($record as $value) {
                $bindings[] = $value;
            }
        }

        $sql = $this->grammar->compileReplace($this, $values);

        $bindings = $this->cleanBindings($bindings);

        return $this->connection->insert($sql, $bindings);
    }
}

laravelがmysqlにクエリを発行するための構文にREPLACEを追加する

MySqlConnection.php
namespace App\Library\Database;

use PDO;
use Illuminate\Database\Schema\MySqlBuilder;
use Illuminate\Database\Query\Processors\MySqlProcessor;
use Doctrine\DBAL\Driver\PDOMySql\Driver as DoctrineDriver;
use Illuminate\Database\Schema\Grammars\MySqlGrammar as SchemaGrammar;
use App\Library\Database\Query\Grammars\MySqlGrammar as QueryGrammar;

class MySqlConnection extends Connection
{
    /**
     * @return \Illuminate\Database\Grammar|\Illuminate\Database\Query\Grammars\MySqlGrammar
     */
    protected function getDefaultQueryGrammar()
    {
        return $this->withTablePrefix(new QueryGrammar);
    }
    /**
     * Get a schema builder instance for the connection.
     *
     * @return \Illuminate\Database\Schema\MySqlBuilder
     */
    public function getSchemaBuilder()
    {
        if (is_null($this->schemaGrammar)) {
            $this->useDefaultSchemaGrammar();
        }

        return new MySqlBuilder($this);
    }
    /**
     * Get the default schema grammar instance.
     *
     */
    protected function getDefaultSchemaGrammar()
    {
        return $this->withTablePrefix(new SchemaGrammar);
    }

    /**
     * Get the default post processor instance.
     *
     * @return \Illuminate\Database\Query\Processors\MySqlProcessor
     */
    protected function getDefaultPostProcessor()
    {
        return new MySqlProcessor;
    }
    /**
     * Get the Doctrine DBAL driver.
     *
     * @return \Doctrine\DBAL\Driver\PDOMySql\Driver
     */
    protected function getDoctrineDriver()
    {
        return new DoctrineDriver;
    }
    /**
     * Bind values to their parameters in the given statement.
     *
     * @param  \PDOStatement $statement
     * @param  array  $bindings
     * @return void
     */
    public function bindValues($statement, $bindings)
    {
        foreach ($bindings as $key => $value) {
            $statement->bindValue(
                is_string($key) ? $key : $key + 1, $value,
                is_int($value) || is_float($value) ? PDO::PARAM_INT : PDO::PARAM_STR
            );
        }
    }
}
MySqlGrammar.php
namespace App\Library\Database\Query\Grammars;

use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Grammars\MySqlGrammar as Grammar;

class MySqlGrammar extends Grammar
{
    /**
     * Compile an replace into statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileReplace(Builder $query, array $values)
    {
        $table = $this->wrapTable($query->from);

        if (! is_array(reset($values))) {
            $values = [$values];
        }

        $columns = $this->columnize(array_keys(reset($values)));

        $parameters = [];

        foreach ($values as $record) {
            $parameters[] = '('.$this->parameterize($record).')';
        }

        $parameters = implode(', ', $parameters);

        return "replace into $table ($columns) values $parameters";
    }
}

mysqlコネクションを差し替える

Illuminate\Database\Connectors\ConnectionFactoryを継承するクラスを作成する

ConnectionFactory.php
namespace App\Library\Database;

use InvalidArgumentException;
use Illuminate\Database\SQLiteConnection;
use Illuminate\Database\PostgresConnection;
use Illuminate\Database\SqlServerConnection;
use Illuminate\Database\Connectors\ConnectionFactory as SuperConnectionFactory;

class ConnectionFactory extends SuperConnectionFactory
{
    /**
     * @inheritdoc
     */
    protected function createConnection($driver, $connection, $database, $prefix = '', array $config = [])
    {
        if ($resolver = Connection::getResolver($driver)) {
            return $resolver($connection, $database, $prefix, $config);
        }

        switch ($driver) {
            case 'mysql':
                return new MySqlConnection($connection, $database, $prefix, $config);
            case 'pgsql':
                return new PostgresConnection($connection, $database, $prefix, $config);
            case 'sqlite':
                return new SQLiteConnection($connection, $database, $prefix, $config);
            case 'sqlsrv':
                return new SqlServerConnection($connection, $database, $prefix, $config);
        }

        throw new InvalidArgumentException("Unsupported driver [{$driver}]");
    }
}

サービスプロバイダーを作成する

DatabaseServiceProvider.php
namespace App\Providers;

use Illuminate\Database\DatabaseManager;
use App\Library\Database\ConnectionFactory;
use Illuminate\Database\DatabaseServiceProvider as ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    /**
     * @inheritdoc
     */
    protected function registerConnectionServices()
    {
        $this->app->singleton('db.factory', function ($app) {
            return new ConnectionFactory($app);
        });

        $this->app->singleton('db', function ($app) {
            return new DatabaseManager($app, $app['db.factory']);
        });

        $this->app->bind('db.connection', function ($app) {
            return $app['db']->connection();
        });
    }
}

DBサービスプロバイダーを差し替える

app.php
'providers' =>[
    
    - Illuminate\Database\DatabaseServiceProvider::class,
    + App\Providers\DatabaseServiceProvider::class,
    
],

使用方法

web.php
Route::get('/', function() {
    DB::table('user_sequences')->replace(['stub' => 'a']);
    echo DB::table('user_sequences')->selectRaw('id')->get();
});

ブラウザをリロードするたびにIDが増えていくのが確認できる
またstubの値を変更すると別のレコードが生成されIDは引き続きインクリメントされる

参考

Laravel support for replace into / insert ignore / insert on duplicate key update
Ticket Servers: Distributed Unique Primary Keys on the Cheap