IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

PHP 单数据库多租户

wen PHP项目 4

本文目录导读：

PHP 单数据库多租户

  1. 架构概述
  2. 数据表设计
  3. 数据库连接类
  4. 基础模型类
  5. 具体模型实现
  6. 租户认证中间件
  7. 控制器示例
  8. 入口文件
  9. 安全优化建议
  10. 性能优化
  11. 租户管理后台

我来详细讲解 PHP 单数据库多租户（Single Database Multi-Tenancy）的完整实现方案。

架构概述

基本概念

  • 单数据库：所有租户共享同一个数据库
  • 区分方式：通过租户ID字段区分数据
  • 优点：成本低、维护简单
  • 缺点：数据隔离性较差

数据表设计

-- 租户表
CREATE TABLE tenants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    domain VARCHAR(100) UNIQUE,
    status TINYINT DEFAULT 1,
    config JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 用户表（带租户ID）
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    password_hash VARCHAR(255),
    role VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_user (tenant_id, username),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);
-- 业务数据表（以订单为例）
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    order_no VARCHAR(32) UNIQUE,
    customer_name VARCHAR(50),
    amount DECIMAL(10,2),
    status TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_order (tenant_id, created_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

数据库连接类

<?php
// Database.php
class Database {
    private static $instances = [];
    private static $tenantId = null;
    public static function setTenantId($tenantId) {
        self::$tenantId = $tenantId;
    }
    public static function getTenantId() {
        return self::$tenantId;
    }
    public static function connection() {
        $config = [
            'host' => 'localhost',
            'database' => 'saas_db',
            'username' => 'root',
            'password' => 'password',
            'charset' => 'utf8mb4'
        ];
        $key = $config['host'] . $config['database'];
        if (!isset(self::$instances[$key])) {
            $dsn = sprintf(
                "mysql:host=%s;dbname=%s;charset=%s",
                $config['host'],
                $config['database'],
                $config['charset']
            );
            self::$instances[$key] = new PDO(
                $dsn,
                $config['username'],
                $config['password'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
        }
        return self::$instances[$key];
    }
}

基础模型类

<?php
// BaseModel.php
abstract class BaseModel {
    protected $table;
    protected $db;
    protected $tenantId;
    public function __construct() {
        $this->db = Database::connection();
        $this->tenantId = Database::getTenantId();
    }
    // 自动注入租户条件
    protected function applyTenantFilter($query, $alias = '') {
        if ($this->tenantId) {
            $column = $alias ? "{$alias}.tenant_id" : "tenant_id";
            $query .= " AND {$column} = :tenant_id";
        }
        return $query;
    }
    // 查询单条记录
    public function find($id) {
        $sql = "SELECT * FROM {$this->table} WHERE id = :id";
        $sql = $this->applyTenantFilter($sql);
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':id', $id);
        if ($this->tenantId) {
            $stmt->bindValue(':tenant_id', $this->tenantId);
        }
        $stmt->execute();
        return $stmt->fetch();
    }
    // 查询多条记录
    public function all($conditions = []) {
        $sql = "SELECT * FROM {$this->table} WHERE 1=1";
        foreach ($conditions as $key => $value) {
            $sql .= " AND {$key} = :{$key}";
        }
        $sql = $this->applyTenantFilter($sql);
        $stmt = $this->db->prepare($sql);
        foreach ($conditions as $key => $value) {
            $stmt->bindValue(":{$key}", $value);
        }
        if ($this->tenantId) {
            $stmt->bindValue(':tenant_id', $this->tenantId);
        }
        $stmt->execute();
        return $stmt->fetchAll();
    }
    // 插入数据
    public function create($data) {
        $data['tenant_id'] = $this->tenantId;
        $columns = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));
        $sql = "INSERT INTO {$this->table} ({$columns}) VALUES ({$placeholders})";
        $stmt = $this->db->prepare($sql);
        foreach ($data as $key => $value) {
            $stmt->bindValue(":{$key}", $value);
        }
        $stmt->execute();
        return $this->db->lastInsertId();
    }
    // 更新数据
    public function update($id, $data) {
        $sets = [];
        foreach ($data as $key => $value) {
            $sets[] = "{$key} = :{$key}";
        }
        $sql = "UPDATE {$this->table} SET " . implode(', ', $sets) . " WHERE id = :id";
        $sql = $this->applyTenantFilter($sql);
        $stmt = $this->db->prepare($sql);
        foreach ($data as $key => $value) {
            $stmt->bindValue(":{$key}", $value);
        }
        $stmt->bindValue(':id', $id);
        if ($this->tenantId) {
            $stmt->bindValue(':tenant_id', $this->tenantId);
        }
        return $stmt->execute();
    }
    // 删除数据
    public function delete($id) {
        $sql = "DELETE FROM {$this->table} WHERE id = :id";
        $sql = $this->applyTenantFilter($sql);
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':id', $id);
        if ($this->tenantId) {
            $stmt->bindValue(':tenant_id', $this->tenantId);
        }
        return $stmt->execute();
    }
}

具体模型实现

<?php
// User.php
class User extends BaseModel {
    protected $table = 'users';
    public function findByUsername($username) {
        $sql = "SELECT * FROM {$this->table} WHERE username = :username";
        $sql = $this->applyTenantFilter($sql);
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':username', $username);
        if ($this->tenantId) {
            $stmt->bindValue(':tenant_id', $this->tenantId);
        }
        $stmt->execute();
        return $stmt->fetch();
    }
}
// Order.php
class Order extends BaseModel {
    protected $table = 'orders';
    // 获取租户的统计数据
    public function getStats() {
        $sql = "SELECT 
                    COUNT(*) as total_orders,
                    SUM(amount) as total_amount,
                    AVG(amount) as avg_amount
                FROM {$this->table}";
        $sql = $this->applyTenantFilter($sql);
        $stmt = $this->db->prepare($sql);
        if ($this->tenantId) {
            $stmt->bindValue(':tenant_id', $this->tenantId);
        }
        $stmt->execute();
        return $stmt->fetch();
    }
    // 分页查询
    public function paginate($page = 1, $limit = 20, $filters = []) {
        $offset = ($page - 1) * $limit;
        $where = "WHERE 1=1";
        foreach ($filters as $key => $value) {
            $where .= " AND {$key} = :{$key}";
        }
        // 获取总数
        $countSql = "SELECT COUNT(*) as total FROM {$this->table} {$where}";
        $countSql = $this->applyTenantFilter($countSql);
        $countStmt = $this->db->prepare($countSql);
        foreach ($filters as $key => $value) {
            $countStmt->bindValue(":{$key}", $value);
        }
        if ($this->tenantId) {
            $countStmt->bindValue(':tenant_id', $this->tenantId);
        }
        $countStmt->execute();
        $total = $countStmt->fetch()['total'];
        // 获取数据
        $dataSql = "SELECT * FROM {$this->table} {$where} 
                    ORDER BY created_at DESC 
                    LIMIT :offset, :limit";
        $dataSql = $this->applyTenantFilter($dataSql);
        $dataStmt = $this->db->prepare($dataSql);
        foreach ($filters as $key => $value) {
            $dataStmt->bindValue(":{$key}", $value);
        }
        if ($this->tenantId) {
            $dataStmt->bindValue(':tenant_id', $this->tenantId);
        }
        $dataStmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $dataStmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $dataStmt->execute();
        $data = $dataStmt->fetchAll();
        return [
            'data' => $data,
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'total_page' => ceil($total / $limit)
        ];
    }
}

租户认证中间件

<?php
// TenantMiddleware.php
class TenantMiddleware {
    public static function handle() {
        session_start();
        // 通过域名识别租户
        $domain = $_SERVER['HTTP_HOST'];
        $tenant = self::getTenantByDomain($domain);
        if (!$tenant) {
            http_response_code(404);
            exit('Tenant not found');
        }
        // 设置当前租户ID
        Database::setTenantId($tenant['id']);
        // 验证登录状态（可选）
        if (!self::validateAuth($tenant)) {
            header('Location: /login');
            exit;
        }
    }
    private static function getTenantByDomain($domain) {
        $db = Database::connection();
        $stmt = $db->prepare("SELECT * FROM tenants WHERE domain = :domain AND status = 1");
        $stmt->execute(['domain' => $domain]);
        return $stmt->fetch();
    }
    private static function validateAuth($tenant) {
        // 检查session中的tenant_id是否匹配
        return isset($_SESSION['tenant_id']) && 
               $_SESSION['tenant_id'] == $tenant['id'];
    }
}

控制器示例

<?php
// UserController.php
class UserController {
    public function index() {
        $userModel = new User();
        $users = $userModel->all();
        // 返回视图
    }
    public function show($id) {
        $userModel = new User();
        $user = $userModel->find($id);
        if (!$user) {
            return ['error' => 'User not found'];
        }
        return $user;
    }
    public function store() {
        $data = $_POST;
        $rules = [
            'username' => 'required|min:3',
            'email' => 'required|email',
            'password' => 'required|min:6'
        ];
        $validator = new Validator($rules);
        if (!$validator->validate($data)) {
            return ['errors' => $validator->getErrors()];
        }
        $userModel = new User();
        $userModel->create($data);
        return ['success' => true, 'message' => 'User created'];
    }
    public function update($id) {
        $data = $_POST;
        $userModel = new User();
        $updated = $userModel->update($id, $data);
        if (!$updated) {
            return ['error' => 'Update failed'];
        }
        return ['success' => true];
    }
    public function destroy($id) {
        $userModel = new User();
        $deleted = $userModel->delete($id);
        if (!$deleted) {
            return ['error' => 'Delete failed'];
        }
        return ['success' => true];
    }
}

入口文件

<?php
// index.php
require_once 'Database.php';
require_once 'BaseModel.php';
require_once 'TenantMiddleware.php';
require_once 'User.php';
require_once 'Order.php';
require_once 'UserController.php';
// 运行租户认证中间件
TenantMiddleware::handle();
// 简单路由
$uri = $_SERVER['REQUEST_URI'];
$method = $_SERVER['REQUEST_METHOD'];
$controller = new UserController();
if ($method == 'GET' && $uri == '/users') {
    echo json_encode($controller->index());
} elseif ($method == 'GET' && preg_match('/\/users\/(\d+)/', $uri, $matches)) {
    echo json_encode($controller->show($matches[1]));
} elseif ($method == 'POST' && $uri == '/users') {
    echo json_encode($controller->store());
} elseif ($method == 'PUT' && preg_match('/\/users\/(\d+)/', $uri, $matches)) {
    echo json_encode($controller->update($matches[1]));
} elseif ($method == 'DELETE' && preg_match('/\/users\/(\d+)/', $uri, $matches)) {
    echo json_encode($controller->destroy($matches[1]));
}

安全优化建议

数据加密

<?php
// 对敏感字段加密
class DataEncryption {
    private static $key = 'your-secret-key';
    public static function encrypt($data) {
        $method = 'AES-256-CBC';
        $iv = openssl_random_pseudo_bytes(16);
        $encrypted = openssl_encrypt($data, $method, self::$key, 0, $iv);
        return base64_encode($iv . $encrypted);
    }
    public static function decrypt($data) {
        $data = base64_decode($data);
        $method = 'AES-256-CBC';
        $iv = substr($data, 0, 16);
        $encrypted = substr($data, 16);
        return openssl_decrypt($encrypted, $method, self::$key, 0, $iv);
    }
}

SQL注入防护

<?php
// 使用预处理语句（已经在BaseModel中实现）
// 额外添加防注入中间件
class SecurityMiddleware {
    public static function filterInput($data) {
        if (is_array($data)) {
            foreach ($data as $key => $value) {
                $data[$key] = self::filterInput($value);
            }
        } else {
            // 移除危险字符
            $data = preg_replace('/([\x00-\x08\x0b\x0c\x0e-\x1f])/', '', $data);
        }
        return $data;
    }
}

性能优化

<?php
// 为租户常用的查询添加索引
-- 示例索引
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);
CREATE INDEX idx_orders_tenant_date ON orders(tenant_id, created_at);
CREATE INDEX idx_users_tenant_role ON users(tenant_id, role);
// 缓存层
class CacheManager {
    private static $cache = [];
    public static function get($key) {
        $cacheKey = Database::getTenantId() . '_' . $key;
        return self::$cache[$cacheKey] ?? null;
    }
    public static function set($key, $value) {
        $cacheKey = Database::getTenantId() . '_' . $key;
        self::$cache[$cacheKey] = $value;
    }
}

租户管理后台

<?php
// TenancyAdmin.php
class TenancyAdmin {
    public function createTenant($name, $domain) {
        $db = Database::connection();
        $stmt = $db->prepare(
            "INSERT INTO tenants (name, domain, status, config) 
             VALUES (:name, :domain, 1, :config)"
        );
        $config = json_encode([
            'max_users' => 100,
            'storage_limit' => '10GB',
            'features' => ['basic', 'advanced']
        ]);
        $stmt->execute([
            'name' => $name,
            'domain' => $domain,
            'config' => $config
        ]);
        return $db->lastInsertId();
    }
    public function deactivateTenant($tenantId) {
        $db = Database::connection();
        $stmt = $db->prepare(
            "UPDATE tenants SET status = 0 WHERE id = :id"
        );
        return $stmt->execute(['id' => $tenantId]);
    }
    public function getTenantUsage($tenantId) {
        $db = Database::connection();
        // 统计用户数
        $userCount = $db->query(
            "SELECT COUNT(*) as count FROM users WHERE tenant_id = $tenantId"
        )->fetch()['count'];
        // 统计订单数
        $orderCount = $db->query(
            "SELECT COUNT(*) as count FROM orders WHERE tenant_id = $tenantId"
        )->fetch()['count'];
        return [
            'users' => $userCount,
            'orders' => $orderCount
        ];
    }
}

优点

  1. 成本低：维护一个数据库，减少基础设施成本
  2. 易于部署：升级维护相对简单
  3. 数据备份简单：只需备份一次

缺点

  1. 数据隔离性差：需要严格的查询条件控制
  2. 性能瓶颈：数据量大时可能出现性能问题
  3. 扩展性受限：难以针对个别租户优化

最佳实践

  1. 始终在查询中加入tenant_id条件
  2. 使用ORM/模型层统一处理租户逻辑
  3. 定期备份，确保数据安全
  4. 监控查询效率，及时优化索引
  5. 为敏感数据加密，加强安全性

这个方案适合中小型SaaS应用，对于数据量和性能要求不高的情况，如果是大型应用，建议考虑"分库"或"分表"的架构。

上一篇PHP 租户识别策略

下一篇PHP 多租户方案设计

相关文章

抱歉，评论功能暂时关闭!