您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP 数据库操作封装：参考 ThinkPHP 设计的轻量级 Model 类 (PDO + 预处理)

編輯：PHP 綜合知識

设计目标

极简依赖：仅需 PHP PDO 扩展
安全第一：全量预处理语句，防 SQL 注入
链式调用：M('user')->where('id',1)->find()
兼容 ThinkPHP 习惯：M() 助手函数、Db::name() 静态调用

1. 配置文件 (config/database.php)

<?php
return [
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'type' => 'mysql',
            'host' => '127.0.0.1',
            'port' => 3306,
            'database' => 'myapp',
            'username' => 'db_user',
            'password' => 'db_pass',
            'charset' => 'utf8mb4',
            'prefix' => 'tp_', // 表前缀
            'options' => [
                \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                \PDO::ATTR_EMULATE_PREPARES => false, // 真预处理
            ],
        ],
    ],
];

2. 核心类 (src/Db.php)

<?php
namespace App\Database;

use PDO;
use PDOException;
use InvalidArgumentException;

class Db
{
    protected static $config = [];
    protected static $instances = [];
    protected static $defaultConnection = 'default';

    protected $pdo;
    protected $connectionName;
    protected $table;
    protected $prefix;
    protected $query = [
        'where' => [],
        'bind' => [],
        'order' => '',
        'limit' => '',
        'field' => '*',
        'group' => '',
        'having' => '',
    ];

    // 单例获取连接
    public static function connect(string $name = null): PDO
    {
        $name = $name ?? self::$defaultConnection;
        if (!isset(self::$instances[$name])) {
            $cfg = self::$config[$name] ?? throw new InvalidArgumentException("数据库配置不存在: $name");
            $dsn = "{$cfg['type']}:host={$cfg['host']};port={$cfg['port']};dbname={$cfg['database']};charset={$cfg['charset']}";
            self::$instances[$name] = new PDO($dsn, $cfg['username'], $cfg['password'], $cfg['options'] ?? []);
        }
        return self::$instances[$name];
    }

    // 静态入口：Db::name('user')
    public static function name(string $table, string $connection = null): self
    {
        $instance = new self();
        $instance->connectionName = $connection ?? self::$defaultConnection;
        $instance->pdo = self::connect($instance->connectionName);
        $instance->table = $table;
        $instance->prefix = self::$config[$instance->connectionName]['prefix'] ?? '';
        return $instance;
    }

    // 助手函数入口：M('user')
    public static function table(string $table, string $connection = null): self
    {
        return self::name($table, $connection);
    }

    // 初始化查询构建器状态
    protected function initQuery(): void
    {
        $this->query = [
            'where' => [], 'bind' => [], 'order' => '', 'limit' => '',
            'field' => '*', 'group' => '', 'having' => '',
        ];
    }

    // ==================== 查询构建器 ====================
    public function field(string $fields): self
    {
        $this->query['field'] = $fields;
        return $this;
    }

    public function where($field, $op = null, $value = null): self
    {
        if (is_array($field)) {
            foreach ($field as $k => $v) {
                $this->where($k, '=', $v);
            }
            return $this;
        }
        if ($value === null) {
            $value = $op;
            $op = '=';
        }
        $this->query['where'][] = "`{$field}` $op ?";
        $this->query['bind'][] = $value;
        return $this;
    }

    public function whereIn(string $field, array $values): self
    {
        if (empty($values)) return $this->where('1', '=', '0'); // 永假
        $placeholders = implode(',', array_fill(0, count($values), '?'));
        $this->query['where'][] = "`{$field}` IN ($placeholders)";
        $this->query['bind'] = array_merge($this->query['bind'], $values);
        return $this;
    }

    public function order(string $order): self
    {
        $this->query['order'] = $order;
        return $this;
    }

    public function limit(int $offset, int $length = null): self
    {
        $this->query['limit'] = $length !== null ? "$offset, $length" : (string)$offset;
        return $this;
    }

    public function page(int $page, int $pageSize = 15): self
    {
        return $this->limit(($page - 1) * $pageSize, $pageSize);
    }

    // ==================== 执行方法 ====================
    protected function buildSelectSql(): string
    {
        $sql = "SELECT {$this->query['field']} FROM `{$this->prefix}{$this->table}`";
        if ($this->query['where']) $sql .= " WHERE " . implode(' AND ', $this->query['where']);
        if ($this->query['group']) $sql .= " GROUP BY {$this->query['group']}";
        if ($this->query['having']) $sql .= " HAVING {$this->query['having']}";
        if ($this->query['order']) $sql .= " ORDER BY {$this->query['order']}";
        if ($this->query['limit']) $sql .= " LIMIT {$this->query['limit']}";
        return $sql;
    }

    public function select(): array
    {
        $this->initQuery();
        $sql = $this->buildSelectSql();
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->query['bind']);
        return $stmt->fetchAll();
    }

    public function find(): ?array
    {
        $this->limit(1);
        $rows = $this->select();
        return $rows[0] ?? null;
    }

    public function value(string $field): mixed
    {
        $this->field($field)->limit(1);
        $row = $this->select();
        return $row ? array_shift($row) : null;
    }

    public function column(string $field): array
    {
        $this->field($field);
        $rows = $this->select();
        return array_column($rows, $field);
    }

    public function count(): int
    {
        $this->field('COUNT(*) AS tp_count');
        $row = $this->select();
        return (int)($row[0]['tp_count'] ?? 0);
    }

    // ==================== 写操作 ====================
    public function insert(array $data): bool
    {
        $this->initQuery();
        $fields = array_keys($data);
        $placeholders = ':' . implode(', :', $fields);
        $sql = "INSERT INTO `{$this->prefix}{$this->table}` (`" . implode('`, `', $fields) . "`) VALUES ($placeholders)";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($data);
    }

    public function insertGetId(array $data): int
    {
        $this->insert($data);
        return (int)$this->pdo->lastInsertId();
    }

    public function update(array $data): int
    {
        $this->initQuery();
        if (empty($this->query['where'])) {
            throw new InvalidArgumentException('update() 必须带 where 条件');
        }
        $sets = [];
        foreach (array_keys($data) as $field) {
            $sets[] = "`{$field}` = :upd_{$field}";
        }
        $sql = "UPDATE `{$this->prefix}{$this->table}` SET " . implode(', ', $sets);
        $sql .= " WHERE " . implode(' AND ', $this->query['where']);

        // 合并绑定参数：where 绑定 + update 绑定 (加前缀避免冲突)
        $bind = $this->query['bind'];
        foreach ($data as $k => $v) {
            $bind["upd_{$k}"] = $v;
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bind);
        return $stmt->rowCount();
    }

    public function delete(): int
    {
        $this->initQuery();
        if (empty($this->query['where'])) {
            throw new InvalidArgumentException('delete() 必须带 where 条件');
        }
        $sql = "DELETE FROM `{$this->prefix}{$this->table}` WHERE " . implode(' AND ', $this->query['where']);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->query['bind']);
        return $stmt->rowCount();
    }

    // ==================== 事务 ====================
    public function transaction(callable $callback): mixed
    {
        $this->pdo->beginTransaction();
        try {
            $result = $callback($this);
            $this->pdo->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    // ==================== 原生 SQL ====================
    public function query(string $sql, array $bind = []): array
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bind);
        return $stmt->fetchAll();
    }

    public function execute(string $sql, array $bind = []): int
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bind);
        return $stmt->rowCount();
    }

    // 获取最后执行的 SQL (调试用)
    public function getLastSql(): string
    {
        return $this->buildSelectSql(); // 简化实现
    }
}

3. 助手函数 (common.php)

<?php
require_once __DIR__ . '/vendor/autoload.php';
require_once __DIR__ . '/src/Db.php';

use App\Database\Db;

// 加载配置
Db::$config = include __DIR__ . '/config/database.php';

// ThinkPHP 风格助手
function M(string $table, string $connection = null)
{
    return Db::table($table, $connection);
}

function Db(string $table = null)
{
    return $table ? Db::name($table) : Db::connect();
}

4. 使用示例

<?php
require_once 'common.php';

// 查询
$user = M('user')->where('status', 1)->where('id', '>', 100)->order('id desc')->limit(10)->select();
$one = M('user')->where('email', 'test@example.com')->find();
$count = M('article')->where('cat_id', 5)->count();
$titles = M('article')->where('status', 1)->column('title');

// 写入
M('user')->insert(['name' => '张三', 'email' => 'zhangsan@example.com', 'created_at' => time()]);
$id = M('user')->insertGetId(['name' => '李四']);

// 更新
M('user')->where('id', 1)->update(['name' => '新名字']);

// 删除
M('user')->where('id', 'in', [1,2,3])->delete();

// 事务
M('user')->transaction(function ($db) {
    $db->name('user')->insert(['name' => 'A']);
    $db->name('log')->insert(['action' => 'create_user']);
    // 抛异常自动回滚
});

// 原生 SQL
Db()->query('SELECT * FROM tp_user WHERE id = ?', [1]);
Db()->execute('UPDATE tp_user SET login_count = login_count + 1 WHERE id = ?', [1]);