A Useful Laravel Package to Easily Work with MySQL Partition

Laravel-mysql-partition is a useful Laravel package to easily work with MySQL Partition. please check the documentation for your MySQL version. Partitioning require a MySQL’s version >= 5.1.0

Installation

Add the package using composer:

$ composer require brokenice/laravel-mysql-partition

Quickstart

Create a migration

From the command line:

php artisan make:migration create_partitioned_table

Then edit the migration you just created by adding one of the partition schema provided by this package;

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Brokenice\LaravelMysqlPartition\Models\Partition;
use Brokenice\LaravelMysqlPartition\Schema\Schema;

class CreatePartitionedTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('partitioned', static function (Blueprint $table) {
            $table->bigInteger('id');
            $table->string('name');
            $table->date('date');
            $table->timestamps();
            $table->primary(['id','date']);
        });
        
        // Force autoincrement of one field in composite primary key
  		Schema::forceAutoIncrement('partitioned', 'id');
  		
  		// Make partition by LIST
        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])
            ]
        );
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('partitioned');
    }
}

Partitions support

The package supports these methods of partitions:

Mysql Partition Types Supports:

• RANGE
• LIST
• HASH
• KEY

Special Partition Types Supports:

• YEARS
• YEARS AND MONTH
• MONTH (coming soon)
• DAYS (coming soon)

Querying partition with Eloquent

Using this package you can query an individual partition, or multiple partitions, directly from the eloquent model:

Create a model

php artisan make:model Partitioned

Then edit the model you just created:

namespace App;

use Illuminate\Database\Eloquent\Model;

class Partitioned extends Model
{
    protected $table = 'partitioned';
}

Query to a single partition

Psy Shell v0.9.9 (PHP 7.3.6 — cli) by Justin Hileman
>>> use App\Models\Partitioned;
>>> Partitioned::partition('name')->first();

Query to multiple partitions

Psy Shell v0.9.9 (PHP 7.3.6 — cli) by Justin Hileman
>>> use App\Models\Partitioned;
>>> Partitioned::partitions(['name', 'name1'])->first();

For more details, you can visit its complete documentation on Github