Edited at

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