php 如何在 Laravel 中使用多个数据库

How to use multiple databases in Laravel

提问by Chintan7027

I want to combine multiple databases in my system. Most of the time the database is MySQL; but it may differ in future i.e. Admin can generate such a reports which is use source of heterogeneousdatabase system.

我想在我的系统中组合多个数据库。大多数时候数据库是MySQL；但将来可能会有所不同，即管理员可以生成这样的报告，该报告使用异构数据库系统的来源。

So my question is does Laravel provide any Facadeto deal with such situations? Or any other framework have more suitable capabilities for problem is?

所以我的问题是Laravel 是否提供任何 Facade来处理这种情况？或者任何其他框架有更适合问题的能力？

回答by Abdulla Nilam

Using .env>= 5.0(tested on 5.5)

使用.env>= 5.0（在 5.5 上测试）

In .env

在 .env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database1
DB_USERNAME=root
DB_PASSWORD=secret

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=database2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=secret

In config/database.php

在 config/database.php

'mysql' => [
    'driver'    => env('DB_CONNECTION'),
    'host'      => env('DB_HOST'),
    'port'      => env('DB_PORT'),
    'database'  => env('DB_DATABASE'),
    'username'  => env('DB_USERNAME'),
    'password'  => env('DB_PASSWORD'),
],

'mysql2' => [
    'driver'    => env('DB_CONNECTION_SECOND'),
    'host'      => env('DB_HOST_SECOND'),
    'port'      => env('DB_PORT_SECOND'),
    'database'  => env('DB_DATABASE_SECOND'),
    'username'  => env('DB_USERNAME_SECOND'),
    'password'  => env('DB_PASSWORD_SECOND'),
],

Note:In mysql2if DB_username and DB_password is same, then you can use env('DB_USERNAME')which is metioned in .envfirst few lines.

注：在mysql2如果DB_username和DB_PASSWORD是一样的，那么你可以使用env('DB_USERNAME')它在文件档案化.env的前几行。

Without .env<5.0

没有.env<5.0

Define Connections

定义连接

app/config/database.php

app/config/database.php

return array(

    'default' => 'mysql',

    'connections' => array(

        # Primary/Default database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => '127.0.0.1',
            'database'  => 'database1',
            'username'  => 'root',
            'password'  => 'secret'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        # Secondary database connection
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => '127.0.0.1',
            'database'  => 'database2',
            'username'  => 'root',
            'password'  => 'secret'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

---

---

Schema

架构

To specify which connection to use, simply run the connection()method

要指定要使用的连接，只需运行该connection()方法

Schema::connection('mysql2')->create('some_table', function($table)
{
    $table->increments('id'):
});

Query Builder

查询生成器

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

Eloquent

雄辩

Set the $connectionvariable in your model

$connection在模型中设置变量

class SomeModel extends Eloquent {

    protected $connection = 'mysql2';

}

You can also define the connection at runtime via the setConnectionmethod or the onstatic method:

您还可以在运行时通过setConnection方法或on静态方法定义连接：

class SomeController extends BaseController {

    public function someMethod()
    {
        $someModel = new SomeModel;

        $someModel->setConnection('mysql2'); // non-static method

        $something = $someModel->find(1);

        $something = SomeModel::on('mysql2')->find(1); // static method

        return $something;
    }

}

NoteBe careful about attempting to build relationships with tables across databases! It is possible to do, but it can come with some caveats and depends on what database and/or database settings you have.

注意尝试与跨数据库的表建立关系时要小心！这是可能的，但它可能带有一些警告，并且取决于您拥有的数据库和/或数据库设置。

---

---

From Laravel Docs

来自 Laravel 文档

Using Multiple Database Connections

使用多个数据库连接

When using multiple connections, you may access each connectionvia the connection method on the DBfacade. The namepassed to the connectionmethod should correspond to one of the connections listed in your config/database.phpconfiguration file:

使用多个连接时，您可以connection通过DBFacade上的连接方法访问每个连接。在name传递给connection方法应该对应于您列出的其中一个连接config/database.php配置文件：

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

You may also access the raw, underlying PDO instance using the getPdo method on a connection instance:

您还可以使用连接实例上的 getPdo 方法访问原始的底层 PDO 实例：

$pdo = DB::connection()->getPdo();

---

---

Useful Links

有用的链接

1. Laravel 5 multiple database connection FROM laracasts.com
2. Connect multiple databases in laravel FROM tutsnare.com
3. Multiple DB Connections in Laravel FROM fideloper.com

1. Laravel 5 多数据库连接FROM laracasts.com
2. 在laravel中连接多个数据库FROM tutsnare.com
3. Laravel FROM 中的多个数据库连接 fideloper.com

回答by schellingerht

In Laravel 5.1, you specify the connection:

在 Laravel 5.1 中，您指定连接：

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

Default, Laravel uses the default connection. It is simple, isn't it?

默认，Laravel 使用默认连接。这很简单，不是吗？

Read more here: http://laravel.com/docs/5.1/database#accessing-connections

在此处阅读更多信息：http: //laravel.com/docs/5.1/database#accessing-connections

回答by sba

Actually, DB::connection('name')->select(..)doesnt work for me, because 'name' has to be in double quotes: "name"

实际上，DB::connection('name')->select(..)对我不起作用，因为 'name' 必须用双引号括起来：“name”

Still, the select query is executed on my default connection. Still trying to figure out, how to convince Laravel to work the way it is intended: change the connection.

尽管如此，选择查询还是在我的默认连接上执行的。仍在试图弄清楚如何说服 Laravel 以预期的方式工作：改变连接。

Edit: I figured it out. After debugging Laravels DatabaseManager it turned out my database.php (config file) (inside $this->app) was wrong. In the section "connections" I had stuff like "database" with values of the one i copied it from. In clear terms, instead of

编辑：我想通了。调试 Laravels DatabaseManager 后发现我的 database.php（配置文件）（在 $this->app 内）是错误的。在“连接”部分，我有像“数据库”这样的东西，其中包含我从中复制的值。明确地说，而不是

env('DB_DATABASE', 'name')

I needed to place something like

我需要放置类似的东西

'myNewName'

since all connections were listed with the same values for the database, username, password, etc. which of course makes little sense if I want to access at least another database name

因为所有连接都使用相同的数据库、用户名、密码等值列出。如果我想访问至少另一个数据库名称，这当然没有意义

Therefore, every time I wanted to select something from another database I always ended up in my default database

因此，每次我想从另一个数据库中选择一些东西时，我总是以我的默认数据库结束

回答by sumit

Laravel has inbuilt support for multiple database systems, you need to provide connection details in config/database.phpfile

Laravel 内置了对多种数据库系统的支持，您需要在config/database.php文件中提供连接详细信息

return [
    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],
'mysqlOne' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST_ONE', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE_ONE', 'forge'),
            'username' => env('DB_USERNAME_ONE', 'forge'),
            'password' => env('DB_PASSWORD_ONE', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],
];

Once you have this you can create two base model class for each connection and define the connection name in those models

一旦你有了这个，你就可以为每个连接创建两个基模型类并在这些模型中定义连接名称

//BaseModel.php
protected $connection = 'mysql';

//BaseModelOne.php
protected $connection = 'mysqlOne';

You can extend these models to create more models for tables in each DB.

您可以扩展这些模型，为每个数据库中的表创建更多模型。