SELECT 基礎語法
-- 查詢所有欄位
SELECT * FROM table_name;
-- 查詢指定欄位
SELECT column1, column2 FROM table_name;
-- 別名
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;
PHP 處理結果集三種方式
1. mysqli_fetch_assoc() - 關聯陣列 (最常用)
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$sql = "SELECT id, username, email, created_at FROM `members` WHERE status = 1";
$result = $mysqli->query($sql);
echo "查詢到 {$result->num_rows} 筆資料\n";
while ($row = $result->fetch_assoc()) {
// $row 是關聯陣列:['id'=>1, 'username'=>'john', ...]
echo "ID: {$row['id']}, User: {$row['username']}, Email: {$row['email']}\n";
}
$result->free(); // 釋放記憶體
$mysqli->close();
?>
2. mysqli_fetch_array() - 數值+關聯雙模式
$result = $mysqli->query("SELECT * FROM `persons`");
while ($row = $result->fetch_array(MYSQLI_BOTH)) {
echo $row[0] . ' ' . $row['FirstName']; // 兩種方式都可存取
}
// MYSQLI_ASSOC / MYSQLI_NUM / MYSQLI_BOTH (預設)
3. mysqli_fetch_object() - 物件模式
$result = $mysqli->query("SELECT * FROM `persons`");
while ($obj = $result->fetch_object()) {
echo $obj->id . ' ' . $obj->FirstName; // 屬性存取
}
// 可指定類別名稱映射
while ($obj = $result->fetch_object('UserEntity')) { ... }
結果集完整操作 API
| 方法/屬性 | 說明 |
$result->num_rows | 結果集行數 (SELECT) |
$result->fetch_assoc() | 取得下一行關聯陣列,無資料回傳 null |
$result->fetch_all(MYSQLI_ASSOC) | 一次取得所有行陣列 (記憶體大量資料慎用) |
$result->data_seek(0) | 重置指標到第一行 |
$result->field_count | 欄位數 |
$result->free() | 釋放結果集記憶體 (重要!) |
輸出 HTML 表格完整範例
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$sql = "SELECT id, username, email, status, created_at FROM `members` ORDER BY id DESC LIMIT 50";
$result = $mysqli->query($sql);
?>
<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>會員列表</title>
<style>
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
tr:nth-child(even) { background-color: #f9f9f9; }
.status-1 { color: green; } .status-0 { color: red; }
</style></head><body>
<h2>會員列表 (共 <?= $result->num_rows ?> 筆)</h2>
<table>
<thead>
<tr>
<th>ID</th><th>使用者名稱</th><th>Email</th><th>狀態</th><th>建立時間</th>
</tr>
</thead>
<tbody>
<?php while ($row = $result->fetch_assoc()): ?>
<tr>
<td><?= htmlspecialchars($row['id']) ?></td>
<td><?= htmlspecialchars($row['username']) ?></td>
<td><?= htmlspecialchars($row['email']) ?></td>
<td class="status-<?= $row['status'] ?>">
<?= $row['status'] ? '正常' : '停用' ?>
</td>
<td><?= htmlspecialchars($row['created_at']) ?></td>
</tr>
<?php endwhile; ?>
</tbody>
</table>
<?php
$result->free();
$mysqli->close();
?>
</body></html>
分頁查詢實作 (高效能版)
<?php
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 20;
$offset = ($page - 1) * $perPage;
// 1. 取得總筆數 (用 COUNT(*), 不要 SELECT * 再數)
$total = $mysqli->query("SELECT COUNT(*) FROM `members` WHERE status=1")->fetch_row()[0];
$totalPages = ceil($total / $perPage);
// 2. 查詢當頁資料 (利用索引覆蓋)
$sql = "SELECT id, username, email, created_at
FROM `members`
WHERE status = 1
ORDER BY id DESC
LIMIT ?, ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('ii', $offset, $perPage);
$stmt->execute();
$result = $stmt->get_result();
// 3. 輸出資料...
while ($row = $result->fetch_assoc()) { ... }
// 4. 分頁導覽
echo '<div class="pagination">';
for ($i = 1; $i <= $totalPages; $i++) {
$class = ($i === $page) ? ' class="active"' : '';
echo "<a href='?page=$i'$class>$i</a> ";
}
echo '</div>';
$stmt->close();
$mysqli->close();
?>
效能優化關鍵點
- 避免 SELECT *:只查需要欄位,減少網路傳輸、利用覆蓋索引
- 善用 LIMIT:分頁、取樣、避免全表掃描回傳大量資料
- WHERE 條件走索引:
EXPLAIN 驗證 type=ref/range、key 有值
- 避免函數運算在索引欄:
WHERE DATE(created_at) = '2024-01-01' → WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
- 釋放結果集:
$result->free() 釋放記憶體,長腳本必要
- 預處理語句:重複查詢編譯一次,防注入
常見錯誤
| 錯誤 | 原因 | 修正 |
| Call to undefined method fetch_assoc() | 用程序式 $result 但呼叫物件方法 | 統一風格:$mysqli->query() 回傳 mysqli_result 物件 |
| Commands out of sync | 多查詢未釋放前一結果集 | 執行下一查詢前 $result->free() |
| 中文亂碼 | 字元集不一致 | $mysqli->set_charset('utf8mb4') + 資料表 utf8mb4 |