Blog Detail

25

Jul
A Useful Laravel Package to Easily Work with MySQL Partition cover image

arrow_back 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

Published at : 25-07-2022

Author : Rizwan Aslam
AUTHOR
Rizwan Aslam

I am a highly results-driven professional with 12+ years of collective experience in the grounds of web application development especially in laravel, native android application development in java, and desktop application development in the dot net framework. Now managing a team of expert developers at Codebrisk.

Launch your project

Launch project