基于SQL与Laravel实现多店铺平台数据库拆分及独立化方案

一、SQL层面操作 #

1. 共享数据库保留表结构 #

先梳理共享库需要留存的核心数据，比如：

• shops：店铺基础信息表（id, name, domain, created_at等）
• shop_sales_stats：销售统计表（shop_id, total_orders, total_sales, updated_at等）

这些表直接保留在原共享数据库中，不参与拆分。

2. 批量创建独立店铺数据库 #

每个店铺的数据库结构完全一致，可通过SQL存储过程批量创建：

DELIMITER //
CREATE PROCEDURE CreateShopDatabase(IN shopId INT, IN dbName VARCHAR(255))
BEGIN
    -- 创建店铺专属数据库
    SET @createDbSql = CONCAT('CREATE DATABASE IF NOT EXISTS ', dbName);
    PREPARE stmt FROM @createDbSql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 在新库中创建订单表（示例，可按需添加products/customers等表）
    SET @createTableSql = CONCAT('CREATE TABLE IF NOT EXISTS ', dbName, '.orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        shop_id INT NOT NULL,
        customer_name VARCHAR(255),
        total_amount DECIMAL(10,2),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )');
    PREPARE stmt FROM @createTableSql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用示例：为ID=1的店铺创建数据库shop_db_1
CALL CreateShopDatabase(1, 'shop_db_1');

3. 历史数据迁移 #

将原共享库中各店铺的数据拆分到对应独立库：

-- 迁移shop_id=1的订单数据到shop_db_1
INSERT INTO shop_db_1.orders (shop_id, customer_name, total_amount, created_at)
SELECT shop_id, customer_name, total_amount, created_at
FROM original_shared_db.orders
WHERE shop_id = 1;

-- 迁移完成后可删除原表对应数据（可选）
DELETE FROM original_shared_db.orders WHERE shop_id = 1;

重复上述语句，替换shop_id和目标数据库名，完成所有店铺的数据迁移。

二、Laravel层面实现 #

1. 多数据库配置 #

在config/database.php中添加共享库和动态店铺库的连接配置：

'connections' => [
    // 共享数据库（默认连接）
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'shared_db'),
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
    ],

    // 动态店铺数据库（数据库名后续动态赋值）
    'mysql_shop' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => '',
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
    ],
],

2. 动态切换数据库连接 #

创建中间件实现请求时自动切换到当前店铺的数据库：

// app/Http/Middleware/ShopDatabaseMiddleware.php
<?php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Support\Facades\Config;

class ShopDatabaseMiddleware
{
    public function handle($request, Closure $next)
    {
        // 从路由参数/会话/请求头获取当前店铺ID，这里以路由参数为例
        $shopId = $request->route('shop_id') ?? session('current_shop_id');
        
        if ($shopId) {
            $dbName = 'shop_db_' . $shopId;
            // 动态配置店铺库连接
            Config::set('database.connections.mysql_shop.database', $dbName);
            // 清除旧连接缓存，确保新连接生效
            \DB::purge('mysql_shop');
            \DB::connection('mysql_shop');
        }

        return $next($request);
    }
}

在app/Http/Kernel.php中注册中间件，可绑定到web路由组或指定路由：

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

3. 模型层适配 #

• 共享库模型（如Shop、ShopSalesStats）直接使用默认连接：

// app/Models/Shop.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Shop extends Model
{
    protected $fillable = ['name', 'domain', 'status'];
}

• 店铺专属模型（如Order、Product）指定使用动态店铺连接：

// app/Models/Order.php
<?php

namespace App\Models;

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

class Order extends Model
{
    protected $connection = 'mysql_shop';
    protected $fillable = ['shop_id', 'customer_name', 'total_amount'];

    // 可选：确保模型初始化时连接正确
    protected static function boot()
    {
        parent::boot();
        $shopId = session('current_shop_id');
        if ($shopId) {
            $dbName = 'shop_db_' . $shopId;
            Config::set('database.connections.mysql_shop.database', $dbName);
            \DB::purge('mysql_shop');
        }
    }
}

4. 销售统计自动同步 #

用模型观察者实现订单创建后自动更新共享库的统计数据：

// app/Observers/OrderObserver.php
<?php

namespace App\Observers;

use App\Models\Order;
use App\Models\ShopSalesStats;

class OrderObserver
{
    public function created(Order $order)
    {
        ShopSalesStats::updateOrCreate(
            ['shop_id' => $order->shop_id],
            [
                'total_orders' => \DB::raw('total_orders + 1'),
                'total_sales' => \DB::raw('total_sales + ' . $order->total_amount),
                'updated_at' => now()
            ]
        );
    }
}

在app/Providers/AppServiceProvider.php中注册观察者：

public function boot()
{
    \App\Models\Order::observe(\App\Observers\OrderObserver::class);
}

三、关键注意事项 #

• 数据库权限：确保Laravel使用的数据库账号拥有CREATE DATABASE权限，以及所有shop_db_*数据库的读写权限。
• 批量结构更新：若需修改店铺库表结构，需编写脚本遍历所有店铺库执行ALTER语句，避免遗漏。
• 连接缓存优化：可将动态连接信息缓存，减少每次请求的配置开销。
• 备份策略：每个店铺库需单独备份，可编写定时脚本遍历所有店铺库执行mysqldump。

内容的提问来源于stack exchange，提问作者عبد الرحمن SSJ4