当前位置：首页 > PHP

php实现mysql

2026-01-13 13:36:07PHP

PHP 连接 MySQL 的基本方法

PHP 提供了多种方式与 MySQL 数据库进行交互，以下是最常用的几种方法。

使用 MySQLi 扩展

MySQLi 是 MySQL 的改进扩展，支持面向对象和面向过程两种编程风格。

面向对象风格示例：

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

面向过程风格示例：

$conn = mysqli_connect("localhost", "username", "password", "database");

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

使用 PDO (PHP Data Objects)

PDO 提供了一个数据访问抽象层，可以支持多种数据库。

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

执行查询操作

MySQLi 查询示例

$sql = "SELECT id, name FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 results";
}

PDO 查询示例

php实现mysql

$stmt = $conn->prepare("SELECT id, name FROM users");
$stmt->execute();

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach($stmt->fetchAll() as $row) {
    echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
}

插入数据操作

MySQLi 插入数据

$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

PDO 插入数据

$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute(['John Doe', 'john@example.com']);

echo "New record created successfully";

关闭连接

MySQLi 关闭连接

$conn->close();

PDO 关闭连接

$conn = null;

安全注意事项

使用预处理语句可以有效防止 SQL 注入攻击。

php实现mysql

MySQLi 预处理示例

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

$name = "John Doe";
$email = "john@example.com";
$stmt->execute();

echo "New record created successfully";

PDO 预处理示例

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);

$name = "John Doe";
$email = "john@example.com";
$stmt->execute();

echo "New record created successfully";

错误处理

MySQLi 错误处理

if (!$conn->query("INSERT INTO users (name) VALUES ('John')")) {
    echo "Error: " . $conn->error;
}

PDO 错误处理

try {
    $conn->query("INVALID SQL");
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

事务处理

MySQLi 事务示例

$conn->autocommit(FALSE);

$conn->query("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
$conn->query("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");

if ($conn->commit()) {
    echo "Transaction completed successfully";
} else {
    $conn->rollback();
    echo "Transaction failed";
}

PDO 事务示例

$conn->beginTransaction();

$conn->exec("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
$conn->exec("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");

if ($conn->commit()) {
    echo "Transaction completed successfully";
} else {
    $conn->rollBack();
    echo "Transaction failed";
}