PHP小码农

Appearance

MySQLi 预处理 ​

概述 ​

MySQLi 预处理语句提供与 PDO 类似的 SQL 注入防护功能。支持 prepare()/bind_param()/execute() 流程，以及 stmt_init() 方式。MySQLi 只支持 ? 问号占位符，不支持命名参数。

适用场景

  • 安全的参数化查询
  • 重复执行的 SQL
  • 所有用户输入的处理

基础概念 ​

bind_param 类型标识 ​

标识类型PHP 类型
iINTEGERint
dDOUBLEfloat
sSTRINGstring
bBLOBstring

语法与代码示例 ​

基本预处理 ​

php
<?php

declare(strict_types=1);

$mysqli = new mysqli('localhost', 'root', 'pass', 'myapp');

// prepare + bind_param + execute
$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ? AND status = ?');

if (!$stmt) {
    die("预处理失败: {$mysqli->error}");
}

$stmt->bind_param('is', $id, $status);

$id = 1;
$status = 'active';
$stmt->execute();

$result = $stmt->get_result();
$user = $result->fetch_assoc();
$stmt->close();

bind_result 获取结果 ​

php
<?php

$stmt = $mysqli->prepare('SELECT id, name, email FROM users WHERE id = ?');
$stmt->bind_param('i', $userId);
$stmt->bind_result($id, $name, $email);

$userId = 1;
$stmt->execute();

while ($stmt->fetch()) {
    echo "ID: {$id}, Name: {$name}, Email: {$email}\n";
}

$stmt->close();

get_result 获取结果集 ​

php
<?php

// get_result 返回 MySQLi Result 对象
$stmt = $mysqli->prepare('SELECT id, name, email FROM users WHERE age > ?');
$stmt->bind_param('i', $minAge);
$stmt->execute();

$result = $stmt->get_result();
$users = $result->fetch_all(MYSQLI_ASSOC);

$stmt->close();

// 优势：可以直接使用 fetch_all、fetch_assoc 等
// 不需要提前知道列名

批量执行预处理 ​

php
<?php

$stmt = $mysqli->prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)');
$stmt->bind_param('ssi', $name, $email, $age);

$mysqli->begin_transaction();

$users = [
    ['Alice', 'alice@example.com', 28],
    ['Bob', 'bob@example.com', 32],
    ['Charlie', 'charlie@example.com', 25],
];

foreach ($users as $user) {
    $name = $user[0];
    $email = $user[1];
    $age = $user[2];
    $stmt->execute();
}

$mysqli->commit();
$stmt->close();

实战示例 ​

MySQLi CRUD 封装 ​

php
<?php

declare(strict_types=1);

class MySQLiCrud
{
    public function __construct(private mysqli $mysqli) {}

    public function find(int $id): ?array
    {
        $stmt = $this->mysqli->prepare('SELECT * FROM users WHERE id = ?');
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row ?: null;
    }

    public function insert(string $name, string $email, int $age): int
    {
        $stmt = $this->mysqli->prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)');
        $stmt->bind_param('ssi', $name, $email, $age);
        $stmt->execute();
        $id = $this->mysqli->insert_id;
        $stmt->close();
        return $id;
    }

    public function update(int $id, string $name): bool
    {
        $stmt = $this->mysqli->prepare('UPDATE users SET name = ? WHERE id = ?');
        $stmt->bind_param('si', $name, $id);
        $result = $stmt->execute();
        $affected = $stmt->affected_rows;
        $stmt->close();
        return $affected > 0;
    }

    public function delete(int $id): bool
    {
        $stmt = $this->mysqli->prepare('DELETE FROM users WHERE id = ?');
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $affected = $stmt->affected_rows;
        $stmt->close();
        return $affected > 0;
    }

    public function search(string $keyword): array
    {
        $like = "%{$keyword}%";
        $stmt = $this->mysqli->prepare('SELECT * FROM users WHERE name LIKE ?');
        $stmt->bind_param('s', $like);
        $stmt->execute();
        $result = $stmt->get_result();
        $rows = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $rows;
    }
}

注意事项 ​

MySQLi 不支持命名参数 ​

php
<?php

// MySQLi 只支持 ? 占位符
$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?');

// 不支持 PDO 风格的 :id 命名占位符
// $stmt = $mysqli->prepare('SELECT * FROM users WHERE id = :id'); // 错误！

bind_param 引用绑定 ​

php
<?php

// bind_param 的参数必须是变量（PHP < 8.0）
$stmt = $mysqli->prepare('INSERT INTO users (name) VALUES (?)');
$stmt->bind_param('s', $name);

$name = 'Alice';
$stmt->execute();

$name = 'Bob';
$stmt->execute(); // 使用新值

// PHP 8.1+ 允许直接传值

最佳实践 ​

1. 使用 get_result 替代 bind_result ​

php
<?php

// 推荐：get_result（不需要预先知道列名）
$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?');
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

// 不推荐：bind_result（需要提前知道列名和数量）
$stmt = $mysqli->prepare('SELECT id, name FROM users WHERE id = ?');
$stmt->bind_param('i', $id);
$stmt->bind_result($id, $name);
$stmt->fetch();

进阶用法 ​

调试与测试技巧 ​

php
<?php
declare(strict_types=1);

// 单元测试辅助函数
function createTestResource(): mixed
{
    return match (true) {
        default => new stdClass(),
    };
}

// 调试输出函数
function debugOutput(mixed , string  = ''): void
{
     =  ? ": " : '';
     .= print_r(, true);
    fwrite(STDERR,  . "\n");
}

// 性能基准测试
function benchmark(callable , int  = 1000): float
{
     = hrtime(true);
    for ($i = 0; $i < $iterations; $i++) {
        $fn();
    }
    return (hrtime(true) - $start) / 1e9;
}

日志记录实践 ​

php
<?php
declare(strict_types=1);

/**
 * 简易日志记录器
 */
class SimpleLogger
{
    private string $logFile;
    private string $level = 'INFO';

    public function __construct(string $logFile)
    {
        $this->logFile = $logFile;
    }

    public function info(string $message, array $context = []): void
    {
        $this->log('INFO', $message, $context);
    }

    public function warning(string $message, array $context = []): void
    {
        $this->log('WARNING', $message, $context);
    }

    public function error(string $message, array $context = []): void
    {
        $this->log('ERROR', $message, $context);
    }

    private function log(string $level, string $message, array $context): void
    {
        $timestamp = date('Y-m-d H:i:s');
        $contextStr = $context ? ' ' . json_encode($context, JSON_UNESCAPED_UNICODE) : '';
        $line = "[{$timestamp}] [{$level}] {$message}{$contextStr}\n";
        file_put_contents($this->logFile, $line, FILE_APPEND | LOCK_EX);
    }
}

配置与环境检测 ​

php
<?php
declare(strict_types=1);

// 环境检测工具
class EnvironmentChecker
{
    public static function checkRequirements(array $requirements): array
    {
        $results = [];
        foreach ($requirements as $name => $check) {
            $results[$name] = is_callable($check) ? $check() : false;
        }
        return $results;
    }

    public static function getSystemInfo(): array
    {
        return [
            'php_version' => PHP_VERSION,
            'os' => PHP_OS,
            'sapi' => PHP_SAPI,
            'memory_limit' => ini_get('memory_limit'),
            'max_execution_time' => ini_get('max_execution_time'),
            'loaded_extensions' => get_loaded_extensions(),
        ];
    }
}

常见问题排查 ​

问题可能原因解决方案
连接超时网络问题/配置错误检查配置，增加超时时间
权限不足文件/目录权限使用 chmod/chown 修正
性能下降索引缺失/数据量大添加索引，优化查询
数据不一致并发冲突/事务残留使用锁机制和事务
内存溢出大数据集/未释放资源增大内存限制，分批处理

故障排除步骤

  1. 检查错误日志和异常信息
  2. 确认配置和环境是否正确
  3. 使用调试工具逐步排查
  4. 参考官方文档查找已知问题

版本兼容性说明 ​

功能最低版本说明
基础功能PHP 8.1本文档基准版本
只读属性PHP 8.1public readonly 修饰符
枚举类型PHP 8.1enum 类型和 match 表达式
FiberPHP 8.1协程/轻量级并发
命名参数PHP 8.0foo(arg_name: value)
联合类型PHP 8.0`int
Null 安全运算符PHP 8.0$obj?->method()
析构器 promotionPHP 8.0__construct(public $x)
php
<?php
declare(strict_types=1);

// 版本兼容性检测
function ensureVersion(string $minVersion): void
{
    if (version_compare(PHP_VERSION, $minVersion, '<')) {
        throw new RuntimeException(
            sprintf('需要 PHP %s+, 当前版本: %s', $minVersion, PHP_VERSION)
        );
    }
}

ensureVersion('8.1.0');

参考链接 ​