Laravel - DB使用 -多库、读写分离、事务、水平分表日期维度关联模型可用

周昊乾

2023-12-01

Laravel改造完整版传送门

参考地址：https://learnku.com/docs/laravel/7.x/database/7493#introduction

配置数据库

配置

数据库的配置文件放置在 config/database.php 文件中，你可以在此定义所有的数据库连接，并指定默认使用的连接。此文件内提供了大部分 Laravel 能支持的数据库配置示例。

打开文件 config/database.php修改

读写分离、多库配置都在这里

<?php

use Illuminate\Support\Str;

return [

	// 修改默认连接test
    'default' => 'test',

    // 数据库连接的配置如下
    'connections' => [
    	// 第一个db配置
        'test' => [
            'driver'         => 'mysql',
            // 读配置
            'read'           => [
                'host'     => env('DB_READ_HOST'),
                'port'     => env('DB_READ_PORT'),
                'username' => env('DB_READ_USERNAME'),
                'password' => env('DB_READ_PASSWORD', ''),
            ],
            // 写配置
            'write'          => [
                'host'     => env('DB_WRITE_HOST'),
                'port'     => env('DB_WRITE_PORT'),
                'username' => env('DB_WRITE_USERNAME'),
                'password' => env('DB_WRITE_PASSWORD', ''),
            ],
            'database'       => env('DB_DATABASE'),
            'unix_socket'    => env('DB_SOCKET', ''),
            'charset'        => 'utf8mb4',
            'collation'      => 'utf8mb4_unicode_ci',
            'prefix'         => '',
            'prefix_indexes' => true,
            'strict'         => true,
            'engine'         => null,
            'options'        => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
    	// 第二个db配置
        'test1'   => [
            'driver'         => 'mysql',
            'read'           => [
                'host'     => env('DB_TEST1_READ_HOST'),
                'port'     => env('DB_TEST1_READ_PORT'),
                'username' => env('DB_TEST1_READ_USERNAME'),
                'password' => env('DB_TEST1_READ_PASSWORD', ''),
            ],
            'write'          => [
                'host'     => env('DB_TEST1_WRITE_HOST'),
                'port'     => env('DB_TEST1_WRITE_PORT'),
                'username' => env('DB_TEST1_WRITE_USERNAME'),
                'password' => env('DB_TEST1_WRITE_PASSWORD', ''),
            ],
            'database'       => env('DB_TEST1_DATABASE'),
            'unix_socket'    => env('DB_SOCKET', ''),
            'charset'        => 'utf8mb4',
            'collation'      => 'utf8mb4_unicode_ci',
            'prefix'         => '',
            'prefix_indexes' => true,
            'strict'         => true,
            'engine'         => null,
            'options'        => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

    ],

    // ... 省略

    // Redis配置 - 与Cache相关，如session使用到redis则也相关
    'redis' => [
        'client'  => 'predis',

        'default' => [
            'host'               => env('REDIS_HOST', '127.0.0.1'),
            'password'           => env('REDIS_PASSWORD', null),
            'port'               => env('REDIS_PORT', '6379'),
            'database'           => 0,
            'read_write_timeout' => 60
        ],

        'cache'   => [
            'host'               => env('REDIS_HOST', '127.0.0.1'),
            'password'           => env('REDIS_PASSWORD', null),
            'port'               => '6380',
            'database'           => 1,
            'read_write_timeout' => 60
        ],

        'session' => [
            'host'               => env('REDIS_HOST', '127.0.0.1'),
            'password'           => env('REDIS_PASSWORD', null),
            'port'               => 6381,
            'database'           => 2
        ],
    ],

];

DB配置完毕~

水平分表

日期维度分表

1. 先编写一个日期插件

<?php

namespace App\Models\Tools\SplitTable;

use DateTime;

/**
 * Trait DateTime
 *
 * 根据日期分表使用
 *
 * @author  wei
 * @package App\Models\Tools\SplitTable
 */
trait TraitDateTime
{
    protected $tableNameSuffixFormat = 'Ym';

    /**
     * 设置表名后缀
     *
     * @param DateTime $datetime
     *
     * @return string
     */
    protected function getTableNameSuffix(DateTime $datetime)
    {
        return $datetime->format($this->tableNameSuffixFormat);
    }

}

使用trait方便其他模型切换hash分表等

2. 编写一个基类

创建一个抽象类app/Models/BaseModel.php方便其他model继承

<?php


namespace App\Models;

use Exception;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;


/**
 * Class BaseModel
 *
 * @author  wei
 * @package App\Models
 */
abstract class BaseModel extends Model
{
}

3. 填充基类

添加设置分表表名的方法使用

    /**
     * @var null|mixed
     */
    protected $splitTableParam = null;
    
    /**
     * 设置分表名称
     *
     * @param mixed $param
     *
     * @return BaseModel
     * @throws Exception
     */
    public function setSplitTableName($param)
    {
        $this->splitTableParam = $param;

        if (! empty($param) && method_exists($this, 'getTableNameSuffix')) {
            return $this->setTable(sprintf("%s_%s", $this->getTable(), call_user_func([$this, 'getTableNameSuffix'], $param)));
        }

        throw new Exception("Method does not exist [getTableNameSuffix]");
    }

添加一个实例方法

     /**
     * 分表实例
     *
     * @param mixed $param
     *
     * @return Builder
     * @throws Exception
     */
    public static function splitTable($param)
    {
        if (method_exists(static::class, 'getTableNameSuffix')) {
            return (new static)->setSplitTableName($param)->newQuery();
        }

        throw new Exception("Method does not exist [getTableNameSuffix]");
    }

这样可以用这种方法调用

AccountRecord::splitTable(new \DateTime())->find(1);

还有一种调用方式是new那么还需要修改一个方法


    /**
     * Create a new instance of the given model.
     *
     * @param array $attributes
     * @param bool  $exists
     *
     * @return static
     * @throws Exception
     */
    public function newInstance($attributes = [], $exists = false)
    {
        $model = parent::newInstance($attributes, $exists);
		
		// empty($this->splitTableParam) 防止重复命名出现 XXX_2020_2020的情况
        if (method_exists(static::class, 'getTableNameSuffix') && empty($this->splitTableParam)) {
            $model->setSplitTableName($this->splitTableParam);
        }

        return $model;
    }

调用方法

(new AccountRecord())->setSplitTableName(new \DateTime())->find(1);

最终结果如下：

<?php


namespace App\Models;

use Exception;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;


/**
 * Class BaseModel
 *
 * @author  wei
 * @package App\Models
 */
abstract class BaseModel extends Model
{
    /**
     * @var null|mixed
     */
    protected $splitTableParam = null;


    /**
     * Create a new instance of the given model.
     *
     * @param array $attributes
     * @param bool  $exists
     *
     * @return static
     * @throws Exception
     */
    public function newInstance($attributes = [], $exists = false)
    {
        $model = parent::newInstance($attributes, $exists);

        if (method_exists(static::class, 'getTableNameSuffix') && empty($this->splitTableParam)) {
            $model->setSplitTableName($this->splitTableParam);
        }

        return $model;
    }


    /**
     * 分表实例
     *
     * @param mixed $param
     *
     * @return Builder
     * @throws Exception
     */
    public static function splitTable($param)
    {
        if (method_exists(static::class, 'getTableNameSuffix')) {
            return (new static)->setSplitTableName($param)->newQuery();
        }

        throw new Exception("Method does not exist [getTableNameSuffix]");
    }


    /**
     * 设置分表名称
     *
     * @param mixed $param
     *
     * @return BaseModel
     * @throws Exception
     */
    public function setSplitTableName($param)
    {
        $this->splitTableParam = $param;

        if (! empty($param) && method_exists($this, 'getTableNameSuffix')) {
            return $this->setTable(sprintf("%s_%s", $this->getTable(), call_user_func([$this, 'getTableNameSuffix'], $param)));
        }

        throw new Exception("Method does not exist [getTableNameSuffix]");
    }

}

4. 子类继承引入Trait

<?php

namespace App\Models\Account;

use App\Models\BaseModel;
use App\Models\Tools\SplitTable\TraitDateTime;

class AccountRecord extends BaseModel
{
    use TraitDateTime;
    
    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);

		// 设置格式
        $this->tableNameSuffixFormat = 'Y';
    }
}

5. 关联模型配置方法

在关联报表中写好模型

<?php

class Account extends BaseModel
{
	// ...
	
    /**
     * 关联日志模型
     *
     * @return HasMany
     * @throws Exception
     */
    public function record()
    {
        $instance   = (new AccountRecord())->setSplitTableName($this->splitTableParam['record']);
        $foreignKey = $instance->getTable() . '.' . $this->getForeignKey();
        $localKey   = $this->getKeyName();

		// 切记new HasMany如果使用$this->hasMany则会有各种错误
        return new HasMany($instance->newQuery(), $this, $foreignKey, $localKey);
    }

}

6. 移步调用方使用

	// 创建、删除等
	$account = TeacherAccount::firstOrNew([...]);
	$account->record()->create([...]);
	$account->record()->delete([...]);
	// 查询
	$account->record;

事务

1、第一种方式：事务闭包，很好用推荐

参考文件：vendor/laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php

use Illuminate\Support\Facades\DB; 

// $attempts 为重试次数
DB::transaction(function () {
    // 这里面写逻辑
    // 这里面如果有逻辑不符合可以抛出异常 在抛异常的时候可以记录日志
    // 如果有异常则会自动回滚 
}, $attempts);

2、第二种方式：常规方式

use Illuminate\Support\Facades\DB; 
//开启事务 
DB::beginTransaction();
try{ 
      //中间逻辑代码
      DB::commit(); 
} catch (\Exception $e) { 
      //接收异常处理并回滚
      DB::rollBack(); 
}