laravel 动态切换数据库连接

Switch db connection dynamically

提问by Parampal Pooni

For several console commands, I have the need to change databases so all my eloquent commands and queries run on the correct db (and server).

对于几个控制台命令，我需要更改数据库，以便我所有雄辩的命令和查询都在正确的数据库（和服务器）上运行。

Ive seen a few solutions, the simplest seems to be changing the config like so:

我见过一些解决方案，最简单的似乎是像这样更改配置：

$new_connection = [
        'driver'    => 'mysql',
        'host'      => '127.0.0.1',
        'database'  => 'test_db',
        'username'  => 'test',
        'password'  => 'test',
        'charset'   => 'utf8',
        'collation' => 'utf8_general_ci',
        'prefix'    => '',
        'strict'    => false
];

config(['database.connections.mysql' => $new_connection]);
DB::purge('mysql');

The only issue (that I have noticed) is when I attempt to do transactions, more specifically, when I do transactions inside my acceptance tests in Codeception - they simply don't work.

唯一的问题（我已经注意到）是当我尝试进行交易时，更具体地说，当我在 Codeception 的验收测试中进行交易时 - 它们根本不起作用。

The commands I use are:

我使用的命令是：

DB::connection()->beginTransaction(); // inside the _before function

and

和

DB::connection()->rollBack(); // inside the _after function

回答by Sylwit

You have to create 2 distincts connections

您必须创建 2 个不同的连接

http://fideloper.com/laravel-multiple-database-connectionshttps://laravel.com/docs/5.1/database#accessing-connections

http://fideloper.com/laravel-multiple-database-connections https://laravel.com/docs/5.1/database#accessing-connections

return array(
    'default' => 'mysql',

    'connections' => array(

        # Our primary database connection
        'mysql' => array(
          'driver'    => 'mysql',
          'host'      => '127.0.0.1',
          'database'  => 'test_db',
          'username'  => 'test',
          'password'  => 'test',
          'charset'   => 'utf8',
          'collation' => 'utf8_general_ci',
          'prefix'    => '',
          'strict'    => false
        ),

        # Our secondary database connection
        'mysql2' => array(
          'driver'    => 'mysql',
          'host'      => '127.0.0.1',
          'database'  => 'test_db_2',
          'username'  => 'test',
          'password'  => 'test',
          'charset'   => 'utf8',
          'collation' => 'utf8_general_ci',
          'prefix'    => '',
          'strict'    => false
        ),
    ),
);

Now when you want to query you have to pass the connection you need

现在，当您要查询时，您必须传递所需的连接

$users = DB::connection('mysql2')->select(...);

As the default one is declared as mysql, you can omit it.

由于默认的声明为 mysql，您可以省略它。

回答by jaysingkar

I was experiencing the similar issue. To use the transaction you would basically need to use @Sylwit 's approach.

我遇到了类似的问题。要使用交易，您基本上需要使用 @Sylwit 的方法。

Create the required database connections. Lets say mysqland mysql1.

创建所需的数据库连接。让我们说mysql和mysql1。

Now in your controller get the connection to the required database as below:

现在在您的控制器中获取到所需数据库的连接，如下所示：

$connection = DB::connection('mysql1'); // replace this to your required connection name

Now, for transaction use the retrieved connection.

现在，对于事务使用检索到的连接。

$connection->beginTransaction(); // inside the _before function

And

和

$connection->rollBack(); // inside the _after function

OR

或者

In your code, you can just add the connection name:

在您的代码中，您只需添加连接名称：

DB::connection('mysql1')->beginTransaction(); // inside the _before function

and

和

DB::connection('mysql1')->rollBack(); // inside the _after function

回答by Indyz

You could pass a string with the name of the connection as argument to the DB::connection() facade.

您可以将带有连接名称的字符串作为参数传递给 DB::connection() 门面。

https://laravel.com/docs/5.3/database#using-multiple-database-connections

https://laravel.com/docs/5.3/database#using-multiple-database-connections

回答by Sari Yono

if you are using phpunit have a look at phpunit.xml

如果你正在使用 phpunit 看看 phpunit.xml

in the bottom you should see the following

在底部你应该看到以下内容

    <php>
    <env name="APP_ENV" value="testing"/>
    <env name="CACHE_DRIVER" value="array"/>
    <env name="SESSION_DRIVER" value="array"/>
    <env name="QUEUE_DRIVER" value="sync"/>
    </php>

you can assign the env variable for your testing database to be used rather than the orginal production database.

您可以为要使用的测试数据库而不是原始生产数据库分配 env 变量。

so create two connections, assign the database connection name in the .env and reference the testing one in the phpunit.xml Good Luck.

所以创建两个连接，在 .env 中分配数据库连接名称并在 phpunit.xml 中引用测试一个 祝你好运。

回答by Kliment

$config = config()->all();
$config['database']['connections']['mysql'] = $newConnection;
Artisan::call('config:clear');
config($config);

I tested this and it gets the job done

我测试了这个，它完成了工作