xchpc-website/Db.class.php

<?php
class Db
{
    /** @var PDO */
    protected static $pdo;
    protected static $cfg = [
        'dsn'      => 'mysql:host=127.0.0.1;dbname=protection_center;charset=utf8mb4',
        'user'     => 'root',
        'pass'     => 'root',
        'options'  => [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_PERSISTENT         => false,
        ],
    ];

    /* 当前 SQL 与参数 */
    protected $sql      = '';
    protected $params   = [];
    protected $table    = '';
    protected $wheres   = [];
    protected $orders   = [];
    protected $limits   = '';

    /* 日志开关 */
    public static $logEnable = false;
    public static $logFile   = __DIR__ . '/sql.log';

    /* ---------- 公有入口 ---------- */

    /** 初始化（可外部覆盖配置） */
    public static function init(array $config = [])
    {
        self::$cfg = array_merge(self::$cfg, $config);
    }

    /** 快速实例化 */
    public static function table(string $table): self
    {
        return (new self)->setTable($table);
    }

    /** 原始 SQL 查询（带参数） */
    public static function query(string $sql, array $params = [])
    {
        return (new self)->execute($sql, $params);
    }

    /** 开启事务 */
    public static function begin(): bool
    {
        self::connect();
        return self::$pdo->beginTransaction();
    }

    /** 提交 */
    public static function commit(): bool
    {
        return self::$pdo->commit();
    }

    /** 回滚 */
    public static function roll(): bool
    {
        return self::$pdo->rollBack();
    }

    /* ---------- 链式构造 ---------- */

    public function setTable(string $table): self
    {
        $this->table  = $table;
        $this->reset();
        return $this;
    }

    public function where($field, $op = null, $val = null): self
    {
        if (is_array($field)) {          // 批量 where(['id'=>1,'status'=>2])
            foreach ($field as $k => $v) {
                $this->wheres[] = [$k, '=', $v];
            }
        } elseif ($val === null) {       // where('id',1)  缺省 =
            $this->wheres[] = [$field, '=', $op];
        } else {
            $this->wheres[] = [$field, $op, $val];
        }
        return $this;
    }
    
    /** IN查询 */
    public function in(string $field, array $values): self
    {
        if (!empty($values)) {
            $this->wheres[] = [$field, 'IN', $values];
        }
        return $this;
    }
    
    /** NOT IN查询 */
    public function notIn(string $field, array $values): self
    {
        if (!empty($values)) {
            $this->wheres[] = [$field, 'NOT IN', $values];
        }
        return $this;
    }

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

    public function limit(int $offset, int $length = null): self
    {
        $this->limits = $length === null ? $offset : "$offset,$length";
        return $this;
    }

    /* ---------- 执行 ---------- */

    /** 查多条 */
    public function get(array $columns = ['*'])
    {
        $col = $columns === ['*'] ? '*' : (
            count($columns) === 1 ? "`{$columns[0]}`" :
            implode('`,`', $columns)
        );
        $sql = "SELECT {$col} FROM `{$this->table}`";
        $this->buildWhere($sql);
        $this->buildOrder($sql);
        $this->buildLimit($sql);
        return $this->execute($sql, $this->params)->fetchAll();
    }

    /** 查单条 */
    public function first(array $columns = ['*'])
    {
        $this->limit(1);
        $list = $this->get($columns);
        return $list[0] ?? null;
    }

    /** 统计 */
    public function count(string $field = '*')
    {
        $sql = "SELECT COUNT({$field}) AS c FROM `{$this->table}`";
        $this->buildWhere($sql);
        $row = $this->execute($sql, $this->params)->fetch();
        return (int)($row['c'] ?? 0);
    }

    /** 插入 返回主键 */
    public function insert(array $data): string
    {
        $keys = array_keys($data);
        $vals = array_fill(0, count($keys), '?');
        $sql  = "INSERT INTO `{$this->table}` (`" . implode('`,`', $keys) . "`) VALUES (" . implode(',', $vals) . ")";
        $this->execute($sql, array_values($data));
        return self::$pdo->lastInsertId();
    }

    /** 更新 */
    public function update(array $data): int
    {
        $set = [];
        foreach ($data as $k => $v) {
            $set[]        = "`{$k}`=?";
            $this->params[] = $v;
        }
        $sql = "UPDATE `{$this->table}` SET " . implode(',', $set);
        $this->buildWhere($sql);
        return $this->execute($sql, $this->params)->rowCount();
    }

    /** 删除 */
    public function delete(): int
    {
        $sql = "DELETE FROM `{$this->table}`";
        $this->buildWhere($sql);
        return $this->execute($sql, $this->params)->rowCount();
    }

    /* ---------- 内部 ---------- */

    protected static function connect()
    {
        if (self::$pdo instanceof PDO) {
            return;
        }
        try {
            self::$pdo = new PDO(
                self::$cfg['dsn'],
                self::$cfg['user'],
                self::$cfg['pass'],
                self::$cfg['options']
            );
        } catch (PDOException $e) {
            die('数据库连接失败: ' . $e->getMessage());
        }
    }

    protected function execute(string $sql, array $params = [])
    {
        self::connect();
        $this->sql    = $sql;
        $this->params = $params;
        if (self::$logEnable) {
            $this->log();
        }
        try {
            $stmt = self::$pdo->prepare($sql);
            $stmt->execute($params);
            $this->reset();
            return $stmt;
        } catch (PDOException $e) {
            $this->reset();
            throw new RuntimeException("SQL 错误: " . $e->getMessage() . "\nSQL: $sql\nPARAMS: " . json_encode($params));
        }
    }

    protected function buildWhere(string &$sql)
    {
        if (!$this->wheres) {
            return;
        }
        $str = [];
        foreach ($this->wheres as list($f, $o, $v)) {
            if (in_array(strtoupper($o), ['IN', 'NOT IN']) && is_array($v)) {
                // 处理IN和NOT IN查询，需要特殊处理数组值
                $placeholders = [];
                foreach ($v as $value) {
                    $placeholders[] = '?';
                    $this->params[] = $value;
                }
                $str[] = "`{$f}` {$o} (" . implode(',', $placeholders) . ")";
            } else {
                // 普通查询条件
                $str[] = "`{$f}` {$o} ?";
                $this->params[] = $v;
            }
        }
        $sql .= ' WHERE ' . implode(' AND ', $str);
    }

    protected function buildOrder(string &$sql)
    {
        if ($this->orders) {
            $sql .= ' ORDER BY ' . implode(',', $this->orders);
        }
    }

    protected function buildLimit(string &$sql)
    {
        if ($this->limits) {
            $sql .= ' LIMIT ' . $this->limits;
        }
    }

    protected function reset()
    {
        $this->sql    = '';
        $this->params = [];
        $this->wheres = [];
        $this->orders = [];
        $this->limits = '';
    }

    protected function log()
    {
        $txt = date('Y-m-d H:i:s') . ' | ' . $this->sql . ' | ' . json_encode($this->params) . PHP_EOL;
        error_log($txt, 3, self::$logFile);
    }
}