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.
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....')
There's now a Composer package for this called brokenice/laravel-mysql-partition:
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])
]
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With