PHP数据库

2024/06/01 00:00:00·2026/05/19 10:23:00

MySQL 连接方式演进

PHP 连接 MySQL 经历了三代 API：

API	状态	说明
`mysql_*`	❌ 已移除（7.0）	不支持预处理语句，有 SQL 注入风险
`mysqli_*`	✅ 可用	MySQL 改进版，支持预处理语句和事务
PDO	✅ 推荐	数据库抽象层，支持 MySQL/PostgreSQL/SQLite 等

现代 PHP 代码应始终使用 PDO 或 mysqli，绝不使用 mysql_*。

CRUD 操作

Create: 创建 (INSERT INTO ...)
Read: 读取 (SELECT ...)
Update: 更新 (UPDATE ...)
Delete: 删除 (DELETE ...)

查询

mysql> set names utf8;
mysql> SELECT name FROM websites;
+---------------+
| name          |
+---------------+
| Google        |
| 淘宝        |
| 菜鸟教程  |
| 微博        |
| Facebook      |
| stackoverflow |
+---------------+
6 rows in set (0.00 sec)

连接MySQL

PDO: PHP 数据对象 Php Data Objects: Extension définissant l'interface pour accéder à une base de données depuis php

new PDO()

<?php
$servername = "localhost";
$username = "username";
$password = "password";
 
try {
    $conn = new PDO("mysql:host=$servername;", $username, $password);
    echo "连接成功"; 
}
catch(PDOException $e)
{
    echo $e->getMessage();
}
?>

关闭连接： $conn = null;

创建数据库

exec($SQL)

<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
    $conn = new PDO("mysql:host=$servername", $username, $password);

    // 设置 PDO 错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE DATABASE myDBPDO";

    // 使用 exec() ，因为没有结果返回
    $conn->exec($sql);

    echo "数据库创建成功<br>";
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

创建表

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
 
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // 设置 PDO 错误模式，用于抛出异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    // 使用 sql 创建数据表
    //MyGuests是表名，id, firstname,...是列名
    $sql = "CREATE TABLE MyGuests (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
    )";
 
    // 使用 exec() ，没有结果返回 
    $conn->exec($sql);
    echo "数据表 MyGuests 创建成功";
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}
 
$conn = null;
?>

代码中为每个列指定其他选项的属性：

NOT NULL - 每一行都必须含有值（不能为空），null 值是不允许的。
DEFAULT value - 设置默认值
UNSIGNED - 使用无符号数值类型，0 及正数
AUTO INCREMENT - 设置 MySQL 字段的值在新增记录时每次自动增长 1
PRIMARY KEY - 设置数据表中每条记录的唯一标识。通常列的 PRIMARY KEY 设置为 ID 数值，与 AUTO_INCREMENT 一起使用。

插入数据

SQL插入数据依如下格式：

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

在PHP中依如下代码格式插入：

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
 
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // 设置 PDO 错误模式，用于抛出异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";
    // 使用 exec() ，没有结果返回 
    $conn->exec($sql);
    echo "新记录插入成功";
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}
 
$conn = null;
?>

多条数据插入

Documentation

$conn->commit()
$conn->rollback()
$conn->beginTransaction();

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
 
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    // 开始事务
    $conn->beginTransaction();
    // SQL 语句
    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES ('John', 'Doe', 'john@example.com')");
    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES ('Mary', 'Moe', 'mary@example.com')");
    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES ('Julie', 'Dooley', 'julie@example.com')");
 
    // 提交事务
    $conn->commit();
    echo "新记录插入成功";
}
catch(PDOException $e)
{
    // 如果执行失败回滚
    $conn->rollback();
    echo $sql . "<br>" . $e->getMessage();
}
 
$conn = null;
?>

示例2: 默认autocommit是开启的，多条数据插入时，会打开transaction，自动也就关闭了autocommit，这样就需要在操作后进行一次commit，保证数据库中的信息更新。


<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Insert multiple records on an all-or-nothing basis */
$sql = 'INSERT INTO fruit
    (name, colour, calories)
    VALUES (?, ?, ?)';//预处理语句

$sth = $dbh->prepare($sql);

foreach ($fruits as $fruit) {
    $sth->execute(array(
        $fruit->name,
        $fruit->colour,
        $fruit->calories,
    ));
}

/* Commit the changes */
$dbh->commit();

/* Database connection is now back in autocommit mode */
?>

预处理语句

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
 
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // 设置 PDO 错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    // 预处理 SQL 并绑定参数
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES (:firstname, :lastname, :email)");
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);
 
    // 插入行
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();
 
    // 插入其他行
    $firstname = "Mary";
    $lastname = "Moe";
    $email = "mary@example.com";
    $stmt->execute();
 
    // 插入其他行
    $firstname = "Julie";
    $lastname = "Dooley";
    $email = "julie@example.com";
    $stmt->execute();
 
    echo "新记录插入成功";
}
catch(PDOException $e)
{
    echo "Error: " . $e->getMessage();
}
$conn = null;
?>

读取数据

SELECT column_name(s) FROM table_name
SELECT * FROM table_name

输出读取数据：

<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";
 
class TableRows extends RecursiveIteratorIterator {
    function __construct($it) { 
        parent::__construct($it, self::LEAVES_ONLY); 
    }
 
    function current() {
        return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
    }
 
    function beginChildren() { 
        echo "<tr>"; 
    } 
 
    function endChildren() { 
        echo "</tr>" . "\n";
    } 
} 
 
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
 
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); 
    $stmt->execute();
 
    // 设置结果集为关联数组
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
        echo $v;
    }
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>

Where

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

ORDER BY

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

UPDATE

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

DELETE

DELETE FROM table_name
WHERE some_column = some_value

maLibSQL.pdo.php

学校给的练习题中的函数

SQLUpdate($sql)
SQLInsert($sql)
SQLGetChamp($sql)
SQLSelect($sql):如果没有结果返回 false
parcoursRs():转换
- MySQL 资源 <=> 关联数组

<?php

include("../libs/maLibSQL.pdo.php");

echo "<pre>";
echo '$SQL = "INSERT INTO users(pseudo,passe) VALUES (\'tom\',\'ig2i\')";' . "\n";
echo 'echo SQLInsert($SQL);' . "\n";
echo "</pre>";

$SQL = "INSERT INTO users(pseudo,passe) VALUES ('tom','ig2i')";
echo SQLInsert($SQL);


echo "<hr /><pre>";
echo '$SQL = "SELECT * FROM users";' . "\n";
echo '$rs = SQLSelect($SQL);' . "\n";
echo '$tab = parcoursRs($rs);' . "\n";
echo 'echo "&lt;pre>";' . "\n";
echo 'print_r($tab);' . "\n";
echo 'echo "&lt;/pre>";' . "\n";
echo "</pre>";

$SQL = "SELECT * FROM users";
$rs = SQLSelect($SQL);
$tab = parcoursRs($rs);
echo "<pre>";
print_r($tab);
echo "</pre>";


echo "<hr /><pre>";
echo '$SQL = "SELECT pseudo FROM users WHERE id=1";' . "\n";
echo 'echo SQLGetChamp($SQL);' . "\n";
echo "</pre>";

$SQL = "SELECT pseudo FROM users WHERE id=1";
echo SQLGetChamp($SQL);


echo "<hr /><pre>";
echo '$SQL = "DELETE FROM users WHERE pseudo =\'tom\'";' . "\n";
echo 'echo SQLDelete($SQL);' . "\n";
echo "</pre>";

$SQL = "DELETE FROM users WHERE pseudo ='tom'";
echo SQLDelete($SQL);

?>

安全最佳实践（2026）

密码存储

永远不要明文存储密码。使用 password_hash() 和 password_verify()：

// 存储密码
$hash = password_hash($password, PASSWORD_DEFAULT); // 自动使用 bcrypt
$stmt = $pdo->prepare('INSERT INTO users (email, password) VALUES (?, ?)');
$stmt->execute([$email, $hash]);

// 验证密码
$user = $stmt->fetch();
if (password_verify($input_password, $user['password'])) {
    // 登录成功
}

SQL 注入防护

始终使用预处理语句（Prepared Statements），绝不拼接用户输入到 SQL 中：

// ❌ 危险：SQL 注入
$sql = "SELECT * FROM users WHERE id = " . $_GET['id'];

// ✅ 安全：预处理语句
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$_GET['id']]);

XSS 防护

输出到 HTML 时，始终用 htmlspecialchars() 转义：

// ❌ 危险
echo $_GET['name'];

// ✅ 安全
echo htmlspecialchars($_GET['name'], ENT_QUOTES, 'UTF-8');