PHP与MySQL是Web开发中最经典的组合之一。PHP提供了多种方式来访问和操作MySQL数据库，主要包括MySQL扩展、MySQLi扩展和PDO（PHP Data Objects）。本章将详细介绍这些方法的使用。
| 扩展类型 | PHP版本 | 特点 | 推荐程度 |
|---|---|---|---|
| MySQL | PHP 4-5.6 | 过程式，已废弃 | 不推荐 |
| MySQLi | PHP 5.0+ | 支持过程式和面向对象，MySQL专用 | 推荐 |
| PDO | PHP 5.1+ | 面向对象，支持多种数据库 | 强烈推荐 |
PHP MySQL函数的基本格式如下：
mysql_function(value, value, ...);<?php
// 数据库连接配置
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
// 执行SQL查询
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
// 处理查询结果
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"]. " - 姓名: " . $row["name"]. " - 邮箱: " . $row["email"]. "<br>";
}
} else {
echo "0 结果";
}
// 关闭连接
$conn->close();
?><?php
// 数据库连接配置
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检查连接
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
echo "连接成功";
// 执行SQL查询
$sql = "SELECT id, name, email FROM users";
$result = mysqli_query($conn, $sql);
// 处理查询结果
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "ID: " . $row["id"]. " - 姓名: " . $row["name"]. " - 邮箱: " . $row["email"]. "<br>";
}
} else {
echo "0 结果";
}
// 关闭连接
mysqli_close($conn);
?><?php
// 数据库连接配置
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
// 创建PDO连接
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// 设置PDO错误模式为异常
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "连接成功";
// 准备SQL语句并执行
$stmt = $conn->prepare("SELECT id, name, email FROM users");
$stmt->execute();
// 设置结果集为关联数组
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
// 遍历结果
foreach($stmt->fetchAll() as $row) {
echo "ID: " . $row["id"]. " - 姓名: " . $row["name"]. " - 邮箱: " . $row["email"]. "<br>";
}
} catch(PDOException $e) {
echo "错误: " . $e->getMessage();
}
// 关闭连接
$conn = null;
?>mysqli_connect() / new mysqli() - 建立数据库连接mysqli_close() / $conn->close() - 关闭数据库连接mysqli_select_db() - 选择数据库mysqli_query() / $conn->query() - 执行SQL查询mysqli_prepare() - 准备SQL语句mysqli_stmt_execute() - 执行预处理语句mysqli_fetch_array() - 从结果集中取得一行作为数字数组或关联数组mysqli_fetch_assoc() - 从结果集中取得一行作为关联数组mysqli_fetch_row() - 从结果集中取得一行作为数字数组mysqli_fetch_object() - 从结果集中取得一行作为对象mysqli_num_rows() - 返回结果集中行的数量mysqli_affected_rows() - 返回前次操作影响的记录行数mysqli_error() - 返回最后操作错误的字符串描述mysqli_errno() - 返回最后操作错误的错误代码<?php
// 使用预处理语句防止SQL注入
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$stmt->bind_param("ssi", $name, $email, $age);
// 设置参数并执行
$name = "张三";
$email = "zhangsan@example.com";
$age = 25;
$stmt->execute();
echo "新记录插入成功";
$stmt->close();
?><?php
$sql = "SELECT id, name, email FROM users WHERE age > 18";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "ID: {$row['id']}, 姓名: {$row['name']}, 邮箱: {$row['email']}<br>";
}
} else {
echo "没有找到符合条件的记录";
}
?><?php
$stmt = $conn->prepare("UPDATE users SET email = ? WHERE id = ?");
$stmt->bind_param("si", $email, $id);
$email = "newemail@example.com";
$id = 1;
$stmt->execute();
echo "记录更新成功";
$stmt->close();
?><?php
$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 1;
$stmt->execute();
echo "记录删除成功";
$stmt->close();
?>预处理语句可以有效防止SQL注入攻击，提高应用程序的安全性。
<?php
// 准备预处理语句
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
// 绑定参数
$stmt->bind_param("ssi", $name, $email, $age);
// 设置参数值
$name = "李四";
$email = "lisi@example.com";
$age = 30;
// 执行语句
if ($stmt->execute()) {
echo "记录插入成功，ID: " . $stmt->insert_id;
} else {
echo "错误: " . $stmt->error;
}
// 关闭语句
$stmt->close();
?><?php
try {
// 准备预处理语句
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
// 绑定参数
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':age', $age);
// 设置参数值
$name = "王五";
$email = "wangwu@example.com";
$age = 28;
// 执行语句
$stmt->execute();
echo "记录插入成功，ID: " . $conn->lastInsertId();
} catch(PDOException $e) {
echo "错误: " . $e->getMessage();
}
?><?php
// 检查连接错误
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
// 检查查询错误
$result = $conn->query($sql);
if (!$result) {
die("查询失败: " . $conn->error);
}
// 或者使用异常处理
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
$conn = new mysqli($servername, $username, $password, $dbname);
// 其他数据库操作...
} catch (mysqli_sql_exception $e) {
echo "数据库错误: " . $e->getMessage();
}
?><?php
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 数据库操作...
} catch(PDOException $e) {
echo "错误: " . $e->getMessage();
// 记录到日志文件
error_log("数据库错误: " . $e->getMessage(), 3, "/var/log/php_errors.log");
}
?><?php
// MySQLi 配置选项
$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_CONNECT_TIMEOUT, 5);
mysqli_real_connect($conn, $servername, $username, $password, $dbname);
// PDO 配置选项
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
];
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password, $options);
?><?php
// PDO 持久连接
$options = [
PDO::ATTR_PERSISTENT => true
];
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password, $options);
?>