PHP MySQLi
OOP and procedural APIs, prepared statements, and differences from PDO.
Key Pointsmysqli is MySQL-only but offers `multi_query` and other specifics.
Two APIs
mysqli offers both OOP and procedural interfaces with identical capability. Use OOP in new code.
PHP
<?php
// 面向对象
$mysqli = new mysqli("127.0.0.1", "user", "pass", "demo", 3306);
if ($mysqli->connect_errno) {
exit("连接失败: " . $mysqli->connect_error);
}
$mysqli->set_charset("utf8mb4");
// 过程式（等价）
$link = mysqli_connect("127.0.0.1", "user", "pass", "demo", 3306);
if (!$link) {
exit("连接失败: " . mysqli_connect_error());
}
mysqli_set_charset($link, "utf8mb4");
echo "两种方式都连接成功\n";
$mysqli->close();
mysqli_close($link);Output
两种方式都连接成功new mysqli(...) does not throw by default; failure only sets connect_errno. Enable mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) — recommended in every project.Queries
PHP
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli("127.0.0.1", "user", "pass", "demo");
$db->set_charset("utf8mb4");
$res = $db->query("SELECT id, name FROM users ORDER BY id");
while ($row = $res->fetch_assoc()) {
echo $row["id"] . " " . $row["name"] . "\n";
}
echo "共 " . $res->num_rows . " 行\n";
$res->free();
$db->close();Output
1 Tom
2 Amy
3 Bob
共 3 行Prepared statements
PHP
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli("127.0.0.1", "user", "pass", "demo");
$db->set_charset("utf8mb4");
$stmt = $db->prepare("SELECT name FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 2;
$stmt->execute();
$name = null;
$stmt->bind_result($name);
$stmt->fetch();
echo $name, "\n";
$stmt->close();
$db->close();Output
AmyThe first argument to bind_param is a type string: i, d, s, b. A wrong type does not error but can break comparisons and sorting.
Differences from PDO
| Aspect | mysqli | PDO |
|---|---|---|
| Databases | 仅 MySQL | 12 种驱动 |
| Named params | 不支持 | 支持 |
| Exceptions | 需 mysqli_report | ERRMODE_EXCEPTION |
| Fetch style | fetch_assoc 等 | FETCH_* 常量 |
| Multi query | multi_query | 不支持（防注入） |
| Async | 支持 | 不支持 |
| Class mapping | 无 | FETCH_CLASS |
multi_query
PHP
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli("127.0.0.1", "user", "pass", "demo");
$sql = "INSERT INTO users (name, email) VALUES ('X', 'x@e.com');
INSERT INTO users (name, email) VALUES ('Y', 'y@e.com');";
$db->multi_query($sql);
do {
if ($res = $db->store_result()) {
$res->free();
}
} while ($db->more_results() && $db->next_result());
echo "执行了 " . $db->affected_rows . " 行（最后一条语句）\n";
$db->close();Output
执行了 1 行（最后一条语句）multi_query runs several statements at once, so concatenation means full injection. Use it only with fully server-generated SQL.Common errors
| Error | Cause | Fix |
|---|---|---|
| Commands out of sync | 上一次结果没 free 就发新查询 | 每次 store_result 后 free |
| No such file or directory | localhost 走 socket 且路径错 | 改用 127.0.0.1 |
| 中文乱码 | 没 set_charset | set_charset("utf8mb4") |
| Too many connections | 连接未关，或并发高 | 用完 close，或上连接池 |
两套 API
mysqli 同时提供面向对象和过程式两套接口，功能完全等价。新代码用面向对象那套。
PHP
<?php
// 面向对象
$mysqli = new mysqli("127.0.0.1", "user", "pass", "demo", 3306);
if ($mysqli->connect_errno) {
exit("连接失败: " . $mysqli->connect_error);
}
$mysqli->set_charset("utf8mb4");
// 过程式（等价）
$link = mysqli_connect("127.0.0.1", "user", "pass", "demo", 3306);
if (!$link) {
exit("连接失败: " . mysqli_connect_error());
}
mysqli_set_charset($link, "utf8mb4");
echo "两种方式都连接成功\n";
$mysqli->close();
mysqli_close($link);Output
两种方式都连接成功new mysqli(...) 默认不抛异常，连接失败只是把 connect_errno 设上。要异常行为得 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)——建议每个项目都加。查询
PHP
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli("127.0.0.1", "user", "pass", "demo");
$db->set_charset("utf8mb4");
$res = $db->query("SELECT id, name FROM users ORDER BY id");
while ($row = $res->fetch_assoc()) {
echo $row["id"] . " " . $row["name"] . "\n";
}
echo "共 " . $res->num_rows . " 行\n";
$res->free();
$db->close();Output
1 Tom
2 Amy
3 Bob
共 3 行预处理
PHP
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli("127.0.0.1", "user", "pass", "demo");
$db->set_charset("utf8mb4");
$stmt = $db->prepare("SELECT name FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 2;
$stmt->execute();
$name = null;
$stmt->bind_result($name);
$stmt->fetch();
echo $name, "\n";
$stmt->close();
$db->close();Output
Amybind_param 的第一个参数是类型字符串：i 整数、d 浮点、s 字符串、b blob。类型写错不会报错，但比较和排序可能出问题。
与 PDO 的差异
| 对比项 | mysqli | PDO |
|---|---|---|
| 支持数据库 | 仅 MySQL | 12 种驱动 |
| 命名占位符 | 不支持 | 支持 |
| 异常 | 需 mysqli_report | ERRMODE_EXCEPTION |
| 取数方式 | fetch_assoc 等 | FETCH_* 常量 |
| 多语句 | multi_query | 不支持（防注入） |
| 异步查询 | 支持 | 不支持 |
| 映射到类 | 无 | FETCH_CLASS |
multi_query
PHP
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli("127.0.0.1", "user", "pass", "demo");
$sql = "INSERT INTO users (name, email) VALUES ('X', 'x@e.com');
INSERT INTO users (name, email) VALUES ('Y', 'y@e.com');";
$db->multi_query($sql);
do {
if ($res = $db->store_result()) {
$res->free();
}
} while ($db->more_results() && $db->next_result());
echo "执行了 " . $db->affected_rows . " 行（最后一条语句）\n";
$db->close();Output
执行了 1 行（最后一条语句）multi_query 一次能执行多条语句，意味着拼接 SQL 就等于打开注入大门。用它的前提是 SQL 完全由服务端生成，绝不掺入用户输入。常见错误
| 错误 | 原因 | 解决 |
|---|---|---|
| Commands out of sync | 上一次结果没 free 就发新查询 | 每次 store_result 后 free |
| No such file or directory | localhost 走 socket 且路径错 | 改用 127.0.0.1 |
| 中文乱码 | 没 set_charset | set_charset("utf8mb4") |
| Too many connections | 连接未关，或并发高 | 用完 close，或上连接池 |