如何在 Laravel 数据库迁移中实现分区

Question

使用Laravel 5.3，我如何实现分区。以下是我尝试在迁移中添加的MySQL表结构。

CREATE TABLE `settings` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `client_id` INT(11) NOT NULL,
    `key` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
    `value` TEXT COLLATE utf8_unicode_ci NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY `settings_id_primary` (`client_id`, `id`),
    UNIQUE KEY `settings_key_unique` (`client_id`, `key`),
    KEY `settings_id_key` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PARTITION BY KEY (`client_id`) PARTITIONS 50;

以下是我迄今为止尝试的内容，但这只是添加列和键。

    Schema::create('settings', function(Blueprint $table) {
        $table->integer('id'); // I can't use increments, because throwing an error when I try to add primary key below
        $table->integer('client_id');
        $table->string('key');
        $table->text('value');
        $table->timestamps();

        $table->primary(['client_id', 'id']);
        $table->unique(['client_id', 'key']);
    });

如何进行分区？如果迁移不支持分区，是否有办法将整个SQL查询转储到迁移中并运行。

- Saumini Navaratnam

---

不，使用Laravel Schema Builder无法实现这个功能。 - Ilya Yaremchuk

Answer 1

我认为这对您有所帮助，

      Schema::create('settings', function(Blueprint $table) {
         $table-> increments('id');
         $table->integer('client_id')->primary();
         $table->string('key');
         $table->text('value');
         $table->timestamps();

         $table->unique(['client_id', 'key']);
       });         

或者

      Schema::create('settings', function(Blueprint $table) {
         $table-> increments('id');
         $table->integer('client_id');
         $table->string('key');
         $table->text('value');
         $table->timestamps();

         $table->primary('client_id');
         $table->unique(['client_id', 'key']);
       });         

我到处搜索，但无法找到关于分区的解决方案。
然而，我的建议是在迁移文件函数的up和down功能中使用以下未准备好的内容。

DB::unprepared()   

在迁移中使用分区运行SQL查询。

例如：

DB::unprepared('create table....')

- Rama Durai

---

分区在哪里？ - Saumini Navaratnam

请使用未经准备或原始的数据库。这可能对您有所帮助。 - Rama Durai

@SauminiNavaratnam 如果这是工作，那就接受它吧。其他人也会从中获得知识。 - Rama Durai

Answer 2

现在有一个名为brokenice/laravel-mysql-partition的Composer包，用于此目的：

https://packagist.org/packages/brokenice

Here's a sample right from the docs:

// You use their extended Schema class:
use Brokenice\LaravelMysqlPartition\Schema\Schema;
// You might also need this (I didn't need it for partitioning by hash):
use Brokenice\LaravelMysqlPartition\Models\Partition;

// I omitted class and method definition boilerplate...

// Create a table as you would normally:
Schema::create('partitioned', static function (Blueprint $table) {
    // ...
});
    
// Now partition it (it will run an ALTER TABLE query):
Schema::partitionByList(
    'partitioned', 
    'id',
    [
        new Partition('server_east', Partition::LIST_TYPE, [1,43,65,12,56,73]),
        new Partition('server_west', Partition::LIST_TYPE, [534,6422,196,956,22])
    ]
);

- Rafał G.