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

PHP 单数据库多租户

wen PHP项目 164

本文目录导读：

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应用，对于数据量和性能要求不高的情况，如果是大型应用，建议考虑"分库"或"分表"的架构。

版权声明：NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播

上一篇PHP 租户识别策略

下一篇PHP 多租户方案设计

相关文章

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

友情链接攻略窝 · 礼包窝 · 配置窝 · 换算窝 · 算法窝 · 查文窝 · 手册窝 · 问答窝 · 全文搜 · 手册库
相关站点礼包窝 · 查文窝