laravellaravel-5

How to implement partition in laravel database migration


Using Laravel 5.3 how can I implement partition. Following is the mysql table structure I'm trying to add in migration.

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;

Below is what I tried so far, but this is only adding columns & keys.

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

How can I do the partition? If there migration doesn't support partition. Is there way I can dump the whole SQL query in the migration and run.


Solution

  • I think it is help to you,

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

    or

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

    I searched everywhere, i can't solution find for partition.
    But,

    My suggestion use, below unprepared into the migration file functions of up and down function

    DB::unprepared()   
    

    in migration to run your SQL query with partition.

    like,

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