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

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

配置数据库

配置

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

打开文件 config/database.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. 先编写一个日期插件

namespace App\Models\Tools\SplitTable;

use DateTime;

/**

* Trait DateTime

*

* 根据日期分表使用

*

*

* @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继承

namespace App\Models;

use Exception;

use Illuminate\Database\Eloquent\Builder;

use Illuminate\Database\Eloquent\Model;

/**

* Class BaseModel

*

*

* @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);

最终结果如下：

namespace App\Models;

use Exception;

use Illuminate\Database\Eloquent\Builder;

use Illuminate\Database\Eloquent\Model;

/**

* Class BaseModel

*

*

* @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

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. 关联模型配置方法

在关联报表中写好模型

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();

}