萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP MySQL 入門教程:創建數據庫、表結構與數據操作

PHP 操作 MySQL 完整入門指南

重要提示:mysql_* 函數已在 PHP 7.0 移除。本文使用現代 PDO(PHP Data Objects)和 mysqli 擴展,展示安全、高效的數據庫操作最佳實踐。

一、數據庫連接配置

PDO 連接(推薦,支援多數據庫)

<?php
// config/database.php
return [
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'my_app',
    'username' => 'app_user',
    'password' => 'secure_password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    '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 COLLATE utf8mb4_unicode_ci",
    ],
];

// 連接實例
function createPdoConnection(array $config): PDO {
    $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']};charset={$config['charset']}";
    return new PDO($dsn, $config['username'], $config['password'], $config['options']);
}

$pdo = createPdoConnection(require 'config/database.php');
?>

mysqli 連接(僅 MySQL,面向過程/物件雙模式)

<?php
// 物件模式(推薦)
$mysqli = new mysqli('127.0.0.1', 'app_user', 'secure_password', 'my_app', 3306);
if ($mysqli->connect_errno) {
    throw new \RuntimeException("連接失敗:{$mysqli->connect_error}");
}
$mysqli->set_charset('utf8mb4');
$mysqli->query("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");

// 面向過程模式
$link = mysqli_connect('127.0.0.1', 'app_user', 'secure_password', 'my_app', 3306);
if (!$link) {
    die("連接失敗:" . mysqli_connect_error());
}
mysqli_set_charset($link, 'utf8mb4');
?>

二、創建數據庫與表結構

1. 創建數據庫

<?php
// PDO
$pdo->exec("CREATE DATABASE IF NOT EXISTS `my_app` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
echo "數據庫創建成功\n";

// mysqli
$mysqli->query("CREATE DATABASE IF NOT EXISTS `my_app` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
if ($mysqli->errno) {
    echo "錯誤:{$mysqli->error}\n";
} else {
    echo "數據庫創建成功\n";
}
?>

2. 創建表結構(遷移腳本風格)

<?php
$sql = <<exec($sql);
echo "表創建成功\n";
?>

三、數據增刪改查(CRUD)核心操作

1. 插入數據(預處理語句,防 SQL 注入)

<?php
// PDO 預處理
$stmt = $pdo->prepare("
    INSERT INTO `users` (`uuid`, `username`, `email`, `password_hash`) 
    VALUES (:uuid, :username, :email, :password_hash)
");

$uuid = 'usr_' . bin2hex(random_bytes(16));
$passwordHash = password_hash('user_password123', PASSWORD_DEFAULT);

$stmt->execute([
    ':uuid' => $uuid,
    ':username' => 'zhangsan',
    ':email' => 'zhang@example.com',
    ':password_hash' => $passwordHash,
]);

$userId = $pdo->lastInsertId();
echo "插入成功,ID:$userId\n";

// 批量插入(事務包裹)
$pdo->beginTransaction();
try {
    $stmt = $pdo->prepare("INSERT INTO `users` (`uuid`, `username`, `email`, `password_hash`) VALUES (?, ?, ?, ?)");
    $users = [
        ['usr_' . bin2hex(random_bytes(16)), 'lisi', 'li@example.com', password_hash('pass', PASSWORD_DEFAULT)],
        ['usr_' . bin2hex(random_bytes(16)), 'wangwu', 'wang@example.com', password_hash('pass', PASSWORD_DEFAULT)],
    ];
    foreach ($users as $user) {
        $stmt->execute($user);
    }
    $pdo->commit();
    echo "批量插入成功\n";
} catch (\Throwable $e) {
    $pdo->rollBack();
    throw $e;
}
?>

2. 查詢數據

<?php
// 單條查詢
$stmt = $pdo->prepare("SELECT * FROM `users` WHERE `id` = ?");
$stmt->execute([1]);
$user = $stmt->fetch(); // FETCH_ASSOC
if ($user) {
    echo "用戶:{$user['username']} ({$user['email']})\n";
}

// 多條查詢
$stmt = $pdo->query("SELECT `id`, `username`, `email` FROM `users` WHERE `status` = 1 ORDER BY `created_at` DESC LIMIT 10");
$users = $stmt->fetchAll();
foreach ($users as $user) {
    echo "- {$user['username']}\n";
}

// 分頁查詢
function getUsersPaginated(PDO $pdo, int $page = 1, int $perPage = 15): array {
    $offset = ($page - 1) * $perPage;
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `status` = 1 ORDER BY `id` DESC LIMIT ? OFFSET ?");
    $stmt->bindValue(1, $perPage, PDO::PARAM_INT);
    $stmt->bindValue(2, $offset, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

// 計算總數
$total = $pdo->query("SELECT COUNT(*) FROM `users` WHERE `status` = 1")->fetchColumn();
?>

3. 更新數據

<?php
$stmt = $pdo->prepare("
    UPDATE `users` 
    SET `email` = :email, `updated_at` = CURRENT_TIMESTAMP 
    WHERE `id` = :id
");
$affected = $stmt->execute([
    ':id' => 1,
    ':email' => 'new_email@example.com',
]);
echo "影響行數:$affected\n";

// 批量更新(CASE WHEN)
$ids = [1, 2, 3];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("
    UPDATE `users` 
    SET `status` = CASE `id` " . 
        implode(' ', array_map(fn($i) => "WHEN ? THEN 0", $ids)) . 
    " END 
    WHERE `id` IN ($placeholders)
");
// 參數:先是 CASE 值,再是 WHERE 值
$params = array_merge(array_fill(0, count($ids), 0), $ids);
$stmt->execute($params);
?>

4. 刪除數據(軟刪除推薦)

<?php
// 軟刪除(推薦)
$stmt = $pdo->prepare("UPDATE `users` SET `status` = 0, `updated_at` = CURRENT_TIMESTAMP WHERE `id` = ?");
$stmt->execute([1]);

// 硬刪除(謹慎使用)
$stmt = $pdo->prepare("DELETE FROM `users` WHERE `id` = ?");
$stmt->execute([1]);
?>

四、事務處理與錯誤處理

<?php
function transfer(PDO $pdo, int $fromUserId, int $toUserId, float $amount): bool {
    $pdo->beginTransaction();
    try {
        // 檢查餘額(行鎖)
        $stmt = $pdo->prepare("SELECT `balance` FROM `accounts` WHERE `user_id` = ? FOR UPDATE");
        $stmt->execute([$fromUserId]);
        $balance = $stmt->fetchColumn();
        
        if ($balance < $amount) {
            throw new \DomainException('餘額不足');
        }
        
        // 扣減
        $pdo->prepare("UPDATE `accounts` SET `balance` = `balance` - ? WHERE `user_id` = ?")
            ->execute([$amount, $fromUserId]);
        
        // 增加
        $pdo->prepare("UPDATE `accounts` SET `balance` = `balance` + ? WHERE `user_id` = ?")
            ->execute([$amount, $toUserId]);
        
        // 記錄流水
        $pdo->prepare("INSERT INTO `transactions` (`from_user`, `to_user`, `amount`, `type`) VALUES (?, ?, ?, 'transfer')")
            ->execute([$fromUserId, $toUserId, $amount]);
        
        $pdo->commit();
        return true;
    } catch (\Throwable $e) {
        $pdo->rollBack();
        error_log("轉賬失敗:{$e->getMessage()}");
        return false;
    }
}
?>

五、安全最佳實踐

  1. 永遠使用預處理語句:prepare() + execute(),絕不拼接 SQL 字符串
  2. 最小權限原則:應用賬號僅擁有必要權限(SELECT/INSERT/UPDATE/DELETE),無 DROP/ALTER
  3. 密碼哈希:password_hash() + password_verify(),禁用 MD5/SHA1
  4. 輸入驗證:類型、長度、格式、業務規則雙重驗證(前端+後端)
  5. 錯誤信息不外露:生產環境關閉詳細錯誤輸出,記錄日志
  6. 連接池/持久連接:高併發場景配置持久連接或連接池

六、常用輔助函數封裝

<?php
class Database {
    private PDO $pdo;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    public function query(string $sql, array $params = []): array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    public function find(string $sql, array $params = []): ?array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch() ?: null;
    }
    
    public function execute(string $sql, array $params = []): int {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }
    
    public function insert(string $table, array $data): int {
        $fields = implode('`, `', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));
        $sql = "INSERT INTO `$table` (`$fields`) VALUES ($placeholders)";
        $this->pdo->prepare($sql)->execute($data);
        return (int)$this->pdo->lastInsertId();
    }
    
    public function update(string $table, array $data, string $where, array $whereParams = []): int {
        $set = implode(', ', array_map(fn($k) => "`$k` = :$k", array_keys($data)));
        $sql = "UPDATE `$table` SET $set WHERE $where";
        $params = array_merge($data, $whereParams);
        $this->pdo->prepare($sql)->execute($params);
        return $this->pdo->rowCount();
    }
    
    public function transaction(callable $callback): mixed {
        $this->pdo->beginTransaction();
        try {
            $result = $callback($this);
            $this->pdo->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

// 使用
$db = new Database($pdo);
$userId = $db->insert('users', ['username' => 'test', 'email' => 'test@test.com', 'password_hash' => 'hash']);
$user = $db->find('SELECT * FROM users WHERE id = ?', [$userId]);
?>
copyright © 萬盛學電腦網 all rights reserved