Laravel 8 多租户应用中基于用户登录的动态数据库切换策略

// config/database.php
'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', ''),
        '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' => null, // 这些将动态填充
        'port' => null,
        'database' => null,
        'username' => null,
        'password' => null,
        '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'),
        ]) : [],
    ],
    // ... 其他连接
],

'default' => env('DB_CONNECTION', 'mysql'), // 默认连接指向主数据库

php artisan make:middleware SetTenantDatabaseConnection

<?php

namespace App\Http\Middleware;

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

class SetTenantDatabaseConnection
{
    /**
     * 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() && Auth::user()->tenant_db_name) {
            $user = Auth::user();

            // 获取主数据库中存储的租户数据库凭证
            $tenantDbName = $user->tenant_db_name;
            $tenantDbUsername = $user->tenant_db_username;
            $tenantDbPassword = $user->tenant_db_password;
            $tenantDbHost = $user->tenant_db_host ?? env('DB_HOST', '127.0.0.1'); // 允许租户有不同的主机

            // 动态配置一个新的数据库连接
            Config::set('database.connections.tenant', [
                'driver' => 'mysql',
                'host' => $tenantDbHost,
                'port' => env('DB_PORT', '3306'),
                'database' => $tenantDbName,
                'username' => $tenantDbUsername,
                'password' => $tenantDbPassword,
                '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'连接为当前请求的默认连接
            DB::setDefaultConnection('tenant');

            // 如果之前有缓存的连接，需要清除并重新连接
            // 这确保了所有后续的DB操作都使用新的默认连接
            // DB::purge('tenant'); // 这一行通常不是必须的，因为我们是新设置的连接
            // DB::reconnect('tenant'); // 如果需要强制刷新连接池，可以使用
        }

        return $next($request);
    }
}

// app/Http/Kernel.php
protected $middlewareGroups = [
    'web' => [
        // ... 其他中间件
        \Illuminate\Session\Middleware\AuthenticateSession::class, // 确保用户已认证
        \App\Http\Middleware\SetTenantDatabaseConnection::class, // 在此之后执行
        // ...
    ],

    'api' => [
        // ... 其他中间件
        'throttle:api',
        \Illuminate\Routing\Middleware\SubstituteBindings::class,
        \App\Http\Middleware\SetTenantDatabaseConnection::class, // 如果API需要认证后切换
    ],
];

// app/Models/User.php
namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array<int, string>
     */
    protected $fillable = [
        'name',
        'email',
        'password',
        'tenant_db_name',
        'tenant_db_username',
        'tenant_db_password',
        'tenant_db_host', // 如果需要
    ];

    /**
     * The attributes that should be hidden for serialization.
     *
     * @var array<int, string>
     */
    protected $hidden = [
        'password',
        'remember_token',
        'tenant_db_password', // 隐藏敏感信息
    ];

    /**
     * The attributes that should be cast.
     *
     * @var array<string, string>
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];
}

// app/Models/Product.php
namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    use HasFactory;

    // 无需指定 $connection，它将使用默认连接（即我们动态设置的'tenant'连接）
    protected $fillable = ['name', 'price', 'quantity'];
}

use App\Models\User; // 假设User模型指向主数据库

// 从主数据库获取用户（如果User模型没有明确指定连接，且默认连接已切换到tenant，则需要指定）
$masterUser = User::on('mysql')->find(1);

// 从租户数据库获取产品（将使用默认的'tenant'连接）
$product = \App\Models\Product::find(1);

// 或者显式指定连接，尽管不是必须的，因为它是默认连接
$productExplicit = \App\Models\Product::on('tenant')->find(1);

// app/Models/MasterUser.php (假设你有一个专门用于主数据库的User模型)
namespace App\Models;

use Illuminate\Foundation\Auth\User as Authenticatable;

class MasterUser extends Authenticatable
{
    protected $connection = 'mysql'; // 明确指定连接到主数据库
    // ... 其他User模型属性和方法
}