Laravel单域名多数据库多租户：基于主用户表登录实现

1. 数据库配置修改 #

打开config/database.php，保留主数据库连接（存储用户、租户基础信息），新增租户连接模板用于动态赋值：

// 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' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
        ],

        // 租户数据库连接模板
        'tenant' => [
            'driver' => 'mysql',
            'host' => env('TENANT_DB_HOST', '127.0.0.1'),
            'port' => env('TENANT_DB_PORT', '3306'),
            'database' => '', // 动态填充用户的tenant_db_name
            'username' => env('TENANT_DB_USERNAME', 'forge'),
            'password' => env('TENANT_DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
        ],
    ],
];

2. 用户表迁移（主数据库） #

给用户表添加租户标识字段，执行迁移将字段写入主库：

// database/migrations/xxxx_xx_xx_xxxxxx_add_tenant_fields_to_users_table.php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up() {
        Schema::table('users', function (Blueprint $table) {
            $table->unsignedBigInteger('tenant_id')->nullable();
            $table->string('tenant_db_name')->unique()->nullable(); // 数据库名需唯一
        });
    }

    public function down() {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn(['tenant_id', 'tenant_db_name']);
        });
    }
};

运行迁移命令：

php artisan migrate

3. 租户连接切换中间件 #

创建中间件，在用户登录后动态切换到对应租户数据库：

php artisan make:middleware SetTenantConnection

编辑中间件文件app/Http/Middleware/SetTenantConnection.php：

<?php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Support\Facades\DB;

class SetTenantConnection
{
    public function handle($request, Closure $next)
    {
        if (auth()->check() && auth()->user()->tenant_db_name) {
            $tenantDbName = auth()->user()->tenant_db_name;
            // 动态更新租户连接的数据库名
            config(['database.connections.tenant.database' => $tenantDbName]);
            // 清除旧连接缓存并重新连接
            DB::purge('tenant');
            DB::connection('tenant')->reconnect();
        }

        return $next($request);
    }
}

在app/Http/Kernel.php的web中间件组中注册该中间件（放在Authenticate之后）：

protected $middlewareGroups = [
    'web' => [
        // ...其他中间件
        \App\Http\Middleware\Authenticate::class,
        \App\Http\Middleware\SetTenantConnection::class,
    ],
];

4. 租户模型基类 #

创建所有店铺相关模型的基类，自动使用租户数据库连接：

// app/Models/TenantModel.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class TenantModel extends Model
{
    protected $connection = 'tenant';
}

示例商品模型：

// app/Models/Product.php
<?php

namespace App\Models;

class Product extends TenantModel
{
    protected $fillable = ['name', 'price', 'stock'];
}

5. 控制器操作示例 #

编写控制器操作租户数据库的数据：

// app/Http/Controllers/ProductController.php
<?php

namespace App\Http\Controllers;

use App\Models\Product;
use Illuminate\Http\Request;

class ProductController extends Controller
{
    public function index()
    {
        // 自动使用租户连接查询
        $products = Product::all();
        return view('products.index', compact('products'));
    }

    public function store(Request $request)
    {
        $validated = $request->validate([
            'name' => 'required|string',
            'price' => 'required|numeric',
            'stock' => 'required|integer',
        ]);

        // 写入当前用户的租户数据库
        Product::create($validated);

        return redirect()->back()->with('success', '商品创建成功');
    }
}

关键注意事项 #

• 租户数据库需提前创建：可在用户创建店铺时执行DB::statement("CREATE DATABASE {$dbName}")自动生成
• 租户库结构同步：执行php artisan migrate --database=tenant将店铺相关表迁移到租户库（需临时指定租户连接的数据库名）
• 超级管理员处理：可在中间件中添加判断，跳过无租户信息的超级用户的连接切换

内容的提问来源于stack exchange，提问作者n0nsensei