Laravel 动态数据库切换：实现多租户应用的用户数据隔离

<?php

return [

    // ... 其他配置

    'connections' => [

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            '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', ''),
            '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'),
            ]) : [],
        ],

        // 这是一个占位符，用于动态连接。
        // 实际的租户连接配置将在运行时构建
        'tenant_connection' => [
            'driver' => 'mysql',
            'host' => '', // 运行时填充
            'port' => '', // 运行时填充
            'database' => '', // 运行时填充
            'username' => '', // 运行时填充
            'password' => '', // 运行时填充
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

        // 您也可以在这里预定义一些固定的租户连接，如果租户数量有限
        'tenant_one_db' => [
            'driver' => 'mysql',
            'host' => '127.0.0.1',
            'port' => '3306',
            'database' => 'tenant_one_database',
            'username' => 'tenant_one_user',
            'password' => 'tenant_one_password',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

    ],

    // ... 其他配置
];

use Illuminate\Support\Facades\DB;

// 从主数据库获取租户信息
$tenant = DB::connection('mysql')->table('tenants')->where('user_id', auth()->id())->first();

if ($tenant) {
    // 假设租户信息包含数据库连接详情
    $tenantDbName = $tenant->db_name;
    $tenantDbUser = $tenant->db_username;
    $tenantDbPass = $tenant->db_password;

    // 动态添加或修改一个数据库连接配置
    config()->set('database.connections.dynamic_tenant', [
        'driver' => 'mysql',
        'host' => '127.0.0.1', // 或从租户信息中获取
        'port' => '3306',
        'database' => $tenantDbName,
        'username' => $tenantDbUser,
        'password' => $tenantDbPass,
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ]);

    // 使用动态连接执行查询
    $tenantUsers = DB::connection('dynamic_tenant')->table('users')->get();

    // 或者，如果您想设置默认连接，以便后续所有操作都使用此连接
    // config(['database.default' => 'dynamic_tenant']);
    // $tenantProducts = DB::table('products')->get(); // 这将使用 'dynamic_tenant' 连接
}

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $connection = 'tenant_one_db'; // 假设这个模型总是连接到 tenant_one_db
    // ...
}

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Config;

abstract class TenantModel extends Model
{
    public function getConnectionName()
    {
        // 尝试从当前请求或会话中获取租户数据库连接名称
        // 这需要您在登录或中间件中设置好
        return Config::get('database.default', 'mysql'); // 默认为主数据库
    }
}

// 您的租户相关模型
class Product extends TenantModel
{
    protected $fillable = ['name', 'price'];
    // ...
}

class Order extends TenantModel
{
    protected $fillable = ['total_amount', 'status'];
    // ...
}

php artisan make:middleware SetTenantDatabase

<?php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\Auth;

class SetTenantDatabase
{
    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \Closure(\Illuminate\Http\Request): (\Illuminate\Http\Response|\Illuminate\Http\RedirectResponse)  $next
     * @return \Illuminate\Http\Response|\Illuminate\Http\RedirectResponse
     */
    public function handle(Request $request, Closure $next)
    {
        if (Auth::check()) {
            // 1. 从主数据库获取租户信息
            // 假设您的User模型中有一个tenant_id，或者您有一个Tenant模型关联User
            $user = Auth::user();
            $tenant = DB::connection('mysql')->table('tenants')->where('id', $user->tenant_id)->first();

            if ($tenant) {
                // 2. 动态配置租户数据库连接
                $connectionName = 'tenant_db_' . $tenant->id; // 为每个租户创建唯一连接名

                Config::set('database.connections.' . $connectionName, [
                    'driver' => 'mysql',
                    'host' => $tenant->db_host ?? env('DB_HOST', '127.0.0.1'),
                    'port' => $tenant->db_port ?? env('DB_PORT', '3306'),
                    'database' => $tenant->db_name,
                    'username' => $tenant->db_username,
                    'password' => $tenant->db_password,
                    'charset' => 'utf8mb4',
                    'collation' => 'utf8mb4_unicode_ci',
                    'prefix' => '',
                    'strict' => true,
                    'engine' => null,
                ]);

                // 3. 设置当前请求的默认数据库连接
                Config::set('database.default', $connectionName);

                // 如果您的Eloquent模型继承自TenantModel，它们将自动使用此连接
                // 否则，您需要显式地使用 DB::connection($connectionName)
            }
        }

        return $next($request);
    }
}

// app/Http/Kernel.php
protected $middlewareGroups = [
    'web' => [
        // ...
        \App\Http\Middleware\SetTenantDatabase::class, // 在认证后执行
    ],
    // ...
];

Route::middleware(['auth', 'set.tenant.database'])->group(function () {
    // 所有这些路由都将使用租户数据库
    Route::resource('products', ProductController::class);
});

protected $routeMiddleware = [
    // ...
    'set.tenant.database' => \App\Http\Middleware\SetTenantDatabase::class,
];