illuminate/database 使用 四

文档：Hyperf

Database: Getting Started - Laravel 10.x - The PHP Framework For Web Artisans

因为hyperf使用illuminate/database，所以按照文章，看illuminate/database代码实现。

一、读写分离

根据文档读写的host可以分开。设置读写分离的前提，做数据库出从同步。
 

1.1 数据库主从同步

参考：mysql5.7 主从配置-CSDN博客

1.2 设置

配置中设置三个数据read、write、sticky。read、write都包含host数组，其配置会和主要配置合并。就是说，比如host相同，应该在read、write中设置对应数据，以覆盖主要设置。

sticky选项是一个可选值，可用于允许在当前请求周期内立即读取已写入数据库的记录。如果启用了sticky选项，并且在当前请求周期中对数据库执行了“写”操作，则任何进一步的“读”操作都将使用“写”连接。这确保了在请求周期内写入的任何数据都可以在同一请求期间立即从数据库中读回。

源码如下：

#Illuminate\Database\Connectors\ConnectionFactory
protected function getReadConfig(array $config)
    {
        return $this->mergeReadWriteConfig(
            $config, $this->getReadWriteConfig($config, 'read')
        );
    }
protected function getWriteConfig(array $config)
    {
        return $this->mergeReadWriteConfig(
            $config, $this->getReadWriteConfig($config, 'write')
        );
    }
protected function mergeReadWriteConfig(array $config, array $merge)
    {
        return Arr::except(array_merge($config, $merge), ['read', 'write']);
    }


#Illuminate\Database\Connection
public function getReadPdo()
    {
        if ($this->transactions > 0) {
            return $this->getPdo();
        }

        if ($this->readOnWriteConnection ||
            ($this->recordsModified && $this->getConfig('sticky'))) {
            return $this->getPdo();
        }

        if ($this->readPdo instanceof Closure) {
            return $this->readPdo = call_user_func($this->readPdo);
        }

        return $this->readPdo ?: $this->getPdo();
    }

1.3 测试

配置如下

#config/database.php
return [
    'migrations' => '', //数据迁移
    //PDO文档 https://www.php.net/manual/zh/pdo.constants.php
    'default' => 'master',
    'connections' => [
        'master' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'database' => 'test',
            'username' => 'root',
            'password' => 'qwe110110',
            'charset' => 'utf8',
            'collation' => 'utf8_general_ci',
            'prefix' => '',
            'port' => '3306',
            'read' => [
                'host' => ['127.0.0.1'],
                'port' => '3307',
            ],
            'write' => [
                'host' => ['127.0.0.1'],
                'port' => '3306',
            ],
        ],
    ]
];

 测试代码

function test1()
{
    new App();
    $query = "concat('-',id)";
    $as = "id";
    //$info1 = DbManager::table('userinfo')->select(['name'])->selectSub($query, $as)->addSelect(['age'])->get();
    $info1 = DbManager::table('userinfo')->select(['name'])->selectSub($query, $as)->addSelect(['age'])->get();
    var_dump($info1);
    $data = $info1->all();
    $insertData = ['name' => 'test1', 'age' => 1];
    $id = DbManager::table('userinfo')->insertGetId($insertData);
    var_dump($id);
    //exit;
    //$sql = "CONCAT('A-',name)";
    //$info2 = DbManager::table('userinfo')->selectSub($sql, "table")->toSql()->dump();
}
test1();

 返回结果

string(29) "Illuminate\Support\Collection"
array(8) {
  [0] =>
  class stdClass#18 (3) {
    public $name =>
    string(3) "123"
    public $id =>
    string(2) "-1"
    public $age =>
    int(22)
  }
  [1] =>
  class stdClass#22 (3) {
    public $name =>
    string(5) "name2"
    public $id =>
    string(2) "-2"
    public $age =>
    int(13)
  }
  [2] =>
  class stdClass#23 (3) {
    public $name =>
    string(5) "name3"
    public $id =>
    string(2) "-3"
    public $age =>
    int(24)
  }
  [3] =>
  class stdClass#24 (3) {
    public $name =>
    string(5) "33321"
    public $id =>
    string(2) "-4"
    public $age =>
    int(0)
  }
  [4] =>
  class stdClass#25 (3) {
    public $name =>
    string(5) "test1"
    public $id =>
    string(2) "-5"
    public $age =>
    int(1)
  }
  [5] =>
  class stdClass#26 (3) {
    public $name =>
    string(5) "test1"
    public $id =>
    string(2) "-6"
    public $age =>
    int(1)
  }
  [6] =>
  class stdClass#27 (3) {
    public $name =>
    string(5) "test1"
    public $id =>
    string(2) "-7"
    public $age =>
    int(1)
  }
  [7] =>
  class stdClass#28 (3) {
    public $name =>
    string(5) "test1"
    public $id =>
    string(2) "-8"
    public $age =>
    int(1)
  }
}
int(9)

 从库：

 主库

 测试成功~

1.4 返回数据处理

从测试结果看，返回的都是对象，因为默认使用PDO::FETCH_OBJ。

参考：PHP: PDO - Manual

源码如下

#Illuminate\Database\Capsule\Manager
public function __construct(Container $container = null)
    {
        $this->setupContainer($container ?: new Container);

        // Once we have the container setup, we will setup the default configuration
        // options in the container "config" binding. This will make the database
        // manager work correctly out of the box without extreme configuration.
        $this->setupDefaultConfiguration();

        $this->setupManager();
    }
protected function setupDefaultConfiguration()
    {
        $this->container['config']['database.fetch'] = PDO::FETCH_OBJ;

        $this->container['config']['database.default'] = 'default';
    }
public function setFetchMode($fetchMode)
    {
        $this->container['config']['database.fetch'] = $fetchMode;

        return $this;
    }

#Illuminate\Database\Connection
protected $fetchMode = PDO::FETCH_OBJ;
public function select($query, $bindings = [], $useReadPdo = true)
    {
        return $this->run($query, $bindings, function ($query, $bindings) use ($useReadPdo) {
            if ($this->pretending()) {
                return [];
            }

            // For select statements, we'll simply execute the query and return an array
            // of the database result set. Each element in the array will be a single
            // row from the database table, and will either be an array or objects.
            $statement = $this->prepared(
                $this->getPdoForSelect($useReadPdo)->prepare($query)
            );

            $this->bindValues($statement, $this->prepareBindings($bindings));

            $statement->execute();

            return $statement->fetchAll();
        });
    }
protected function prepared(PDOStatement $statement)
    {
        $statement->setFetchMode($this->fetchMode);

        $this->event(new StatementPrepared(
            $this, $statement
        ));

        return $statement;
    }

想返回数组可以设置为PDO::FETCH_ASSOC。最简单的方法就是替换源码，二次开发最好就是用对象，或者写个对象转数组的方法，tp里面有源码。tp是查出来对象，可使用toArray()搞成数组。

get()返回的类是Illuminate\Support\Collection，此类中还有一些数据统计的方法，比如平均值之类。意思就是查出来数据再做数据统计。

二、多库配置

根据laravel文档：如果应用程序在config/database.php配置文件中定义了多个连接，则可以通过DB facade提供的连接方法访问每个连接。

use Illuminate\Support\Facades\DB;
 
$users = DB::connection('sqlite')->select(/* ... */);
$pdo = DB::connection()->getPdo();

但是仅用这个库由于没有对应数据，会返回db类是null。

源码如下：

#Illuminate\Support\Facades\Facade
public static function getFacadeRoot()
    {
        return static::resolveFacadeInstance(static::getFacadeAccessor());
    }
public static function __callStatic($method, $args)
    {
        $instance = static::getFacadeRoot();
        if (!$instance) {
            throw new RuntimeException('A facade root has not been set.');
        }

        return $instance->$method(...$args);
    }
 protected static function resolveFacadeInstance($name)
    {
        if (is_object($name)) {
            return $name;
        }

        if (isset(static::$resolvedInstance[$name])) {
            return static::$resolvedInstance[$name];
        }
        if (static::$app) {
            return static::$resolvedInstance[$name] = static::$app[$name];
        }
    }

根据方法resolveFacadeInstance(),$name应该是‘db’,不是对象，$resolvedInstance、$app没有对应对象，所以返回空。估计得配合其他类库。比如hyperf中需要

composer require hyperf/database

 那么不用其他类库怎么实现多库切换……

多库配置

return [
    'migrations' => '', //数据迁移
    //PDO文档 https://www.php.net/manual/zh/pdo.constants.php
    'default' => 'master',
    'connections' => [
        'master' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'database' => 'test',
            'username' => 'root',
            'password' => 'mima',
            'charset' => 'utf8',
            'collation' => 'utf8_general_ci',
            'prefix' => '',
            'port' => '3306',
            'read' => [
                'host' => ['127.0.0.1'],
                'port' => '3307',
            ],
            'write' => [
                'host' => ['127.0.0.1'],
                'port' => '3306',
            ],
        ],
        'test' => [
            "driver" => "mysql",
            "host" => "127.0.0.1",
            "database" => "test1",
            "username" => "root",
            "password" => "mima",
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'port' => '3307',
        ],
    ],
];

function test2()
{
    $app = new App();
    $info = DbManager::connection('test')->table('userinfo')->where('id', '=', 1)->get();
    $info = $info->first();
    var_dump($info);
}

test2();

 返回数据

class stdClass#20 (2) {
  public $id =>
  int(1)
  public $name =>
  string(1) "1"
}