laravel 在laravel中动态连接多个数据库

Connect multiple databases dynamically in laravel

提问by Kanth

I'm building an application which requires connecting 2 database. first one is static and another one is dynamic. config/database.php is like

我正在构建一个需要连接 2 个数据库的应用程序。第一个是静态的，另一个是动态的。config/database.php 就像

'mysql' => 
array (
  'driver' => 'mysql',
  'host' => '127.0.0.1',
  'port' => '3306',
  'database' => 'blog',
  'username' => 'root',
  'password' => '',
  'unix_socket' => '',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_unicode_ci',
  'prefix' => '',
  'strict' => true,
  'engine' => NULL,
),
'business2' => 
array (
  'driver' => 'mysql',
  'host' => '127.0.0.1',
  'port' => '3306',
  'database' => 'blog2',
  'username' => 'root',
  'password' => '',
  'unix_socket' => '',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_unicode_ci',
  'prefix' => '',
  'strict' => true,
  'engine' => NULL,
),

and model code is like

和模型代码就像

Class  TableNewData extends Model
{
    protected $connection = 'business3';
    protected $table = 'table2_data';
    public function getData()
    {
        return $this->get()->toArray();
    }
}

I am able to connect multiple databases if I give static connection details but I am unable to connect database if I give dynamic connection details like

如果我提供静态连接详细信息，我可以连接多个数据库，但如果我提供动态连接详细信息，我将无法连接数据库

 $connection = Session::get()->connection;  

or

或者

$connection=$_SESSION('connection');

What is the best way to connect multiple databases dynamically without effecting performance of application?

在不影响应用程序性能的情况下动态连接多个数据库的最佳方法是什么？

采纳答案by DigitalDrifter

One way of changing the connection at runtime is to set the values via the config:

在运行时更改连接的一种方法是通过配置设置值：

config(['database.connections.mysql' => [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST', 'localhost'),
    'database'  => env('DB_DATABASE', 'my_database'),
    'username'  => env('DB_USERNAME', 'my_user'),
    'password'  => env('DB_PASSWORD', 'my_password'),
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
    'strict'    => false,
]]);

This can be applied in a middleware to dynamically switch between tenant databases, for example.

例如，这可以应用于中间件以在租户数据库之间动态切换。

You can also specify a connection via the DB facade:

您还可以通过 DB Facade 指定连接：

DB::connection('mysql_2')->select(...);

回答by Fred

I had the same problem as you. This blog can definitely help you out.

我和你有同样的问题。这个博客绝对可以帮助你。

The Ultimate Guide for Laravel Multi Tenant with Multi Database

具有多数据库的 Laravel 多租户终极指南

Here is how the config/database.php file looks like based on your situation. Since the second one is dynamic, there is no need to define the database.

根据您的情况，config/database.php 文件如下所示。由于第二个是动态的，所以不需要定义数据库。

'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'blog'),
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', 'password'),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => 'InnoDB',
    ],
'business' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => '',
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', 'password'),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => 'InnoDB',
    ], 

Basically, set up a database helper function which connects to the database at runtime and then calls it in the right middleware.I just put the helper file at database/utilities/helpers.php

基本上，设置一个数据库辅助函数，它在运行时连接到数据库，然后在正确的中间件中调用它。我只是将辅助文件放在 database/utilities/helpers.php

function connect($hostname, $username, $password, $database)
{
    // Erase the tenant connection, thus making Laravel get the default values all over again.
    DB::purge('business');
    // Make sure to use the database name we want to establish a connection.
    Config::set('database.connections.tenant.host', $hostname);
    Config::set('database.connections.tenant.database', $database);
    Config::set('database.connections.tenant.username', $username);
    Config::set('database.connections.tenant.password', $password);
    // Rearrange the connection data
    DB::reconnect('business');
    // Ping the database. This will throw an exception in case the database does not exists.
    Schema::connection('tenant')->getConnection()->reconnect();
}

Don't forget to tell the composer that the helper function can be used globally by adding those line into the composer.json file.

不要忘记通过将这些行添加到 composer.json 文件来告诉作曲家可以全局使用辅助函数。

"autoload": {
    "classmap": [
        "database"
    ],
    "files":[
        "database/utilities/helpers.php"
    ],
    "psr-4": {
        "App\": "app/"
    }
},

You also want to have static and dynamic models that should be extended to define which database connections to use.

您还希望拥有应该扩展的静态和动态模型，以定义要使用的数据库连接。

class StaticModel extends Model
{
    protected $connection = 'mysql';
}
class DynamicModel extends Model
{
    protected $connection = 'business';
}

In the middleware set up the dynamic database connection according to the database name.

在中间件中根据数据库名称建立动态数据库连接。

connect(getenv('DB_HOST'), getenv('DB_USERNAME'), getenv('DB_PASSWORD'), getenv('DB_SYMBOL') . $databasename);

Thus, you can use the model as normal but it has the dynamic database connections

因此，您可以正常使用模型，但它具有动态数据库连接