MySQL 插入與查詢數據核心命令詳解

MySQL 插入與查詢數據核心命令詳解

掌握 INSERT 與 SELECT 的完整語法、參數化寫法、PHP 交互模式、結果集處理、內存釋放，是 MySQL 應用開發的基石。本文系統講解命令行與 PHP 兩種場景下的最佳實踐。

一、INSERT 插入數據

1. 標準語法

-- 全列插入（須匹配表定義順序）
INSERT INTO table_name VALUES (v1, v2, ..., vN);

-- 指定列插入（推薦：順序可調、擴展性強、未指定列用默認值）
INSERT INTO table_name (col1, col2, ..., colN) VALUES (v1, v2, ..., vN);

-- 批量插入（單條 SQL，性能比循環插入高數量級）
INSERT INTO table_name (col1, col2) VALUES 
    (v11, v12), (v21, v22), ..., (vN1, vN2);

規則：字符/日期類型值必須用單引號 'value'；數值類型無需引號；NULL 不加引號。

2. 命令行交互示例

mysql> USE tutorials_db;
mysql> INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) 
    -> VALUES ("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) 
    -> VALUES ("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) 
    -> VALUES ("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)

提示：-> 為續行提示符，非 SQL 一部分。NOW() 返回當前 datetime。自增主鍵 tutorial_id 無需指定。

3. PHP 腳本插入（mysqli 預處理模式 - 推薦）

<?php
$host = 'localhost'; $user = 'root'; $pass = 'rootpassword'; $db = 'TUTORIALS';
$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_errno) die('Connect failed: ' . $mysqli->connect_error);
$mysqli->set_charset('utf8mb4');

$title = $_POST['tutorial_title'] ?? '';
$author = $_POST['tutorial_author'] ?? '';
$date = $_POST['submission_date'] ?? date('Y-m-d');

// 預處理：防注入、性能優化
$stmt = $mysqli->prepare("INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)");
$stmt->bind_param('sss', $title, $author, $date);

if ($stmt->execute()) {
    echo "Inserted ID: " . $stmt->insert_id;
} else {
    echo "Error: " . $stmt->error;
}
$stmt->close();
$mysqli->close();
?>

舊版 mysql 擴展已棄用，請直接使用 mysqli 或 PDO。

二、SELECT 查詢數據

1. 標準語法

SELECT col1, col2, ... FROM table_name 
[WHERE condition] 
[GROUP BY col] 
[HAVING condition] 
[ORDER BY col [ASC|DESC]] 
[LIMIT offset, row_count];

• * 代表所有列（生產環境慎用，明確列名）
• WHERE 支持比較、邏輯、IN、BETWEEN、LIKE、IS NULL 等
• LIMIT 偏移量從 0 開始

2. 命令行查詢示例

mysql> SELECT * FROM tutorials_tbl;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 1           | Learn PHP      | John Poul       | 2007-05-21      |
| 2           | Learn MySQL    | Abdul S         | 2007-05-21      |
| 3           | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.01 sec)

3. PHP 處理結果集（mysqli 面向對象）

<?php
$mysqli = new mysqli('localhost', 'root', 'rootpassword', 'TUTORIALS');
$mysqli->set_charset('utf8mb4');

$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
$result = $mysqli->query($sql);
if (!$result) die('Query failed: ' . $mysqli->error);

// 方式一：關聯數組遍歷 (推薦，可讀性強)
while ($row = $result->fetch_assoc()) {
    echo "ID: {$row['tutorial_id']}
";
    echo "Title: {$row['tutorial_title']}
";
    echo "Author: {$row['tutorial_author']}
";
    echo "Date: {$row['submission_date']}
";
}

// 方式二：數字索引數組
// while ($row = $result->fetch_row()) { echo $row[0] . ' ' . $row[1] . '...'; }

// 方式三：對象
// while ($obj = $result->fetch_object()) { echo $obj->tutorial_title; }

// 釋放結果集內存（重要！長連接/大結果集必須釋放）
$result->free();

// 關閉連接
$mysqli->close();
?>

4. PDO 通用寫法（支持多種數據庫）

<?php
$pdo = new PDO("mysql:host=localhost;dbname=TUTORIALS;charset=utf8mb4", 'root', 'rootpassword');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->query("SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: {$row['tutorial_id']}
Title: {$row['tutorial_title']}
...";
}
// PDOStatement 銷毀自動釋放結果集
?>

三、結果集處理函數對比

四、關鍵最佳實踐總結

1. 參數化查詢：prepare() + bind_param() / PDO::prepare()，杜絕 SQL 注入
2. 字符集統一：連接、表、列、PHP 文件均為 utf8mb4
3. 明確列名：SELECT col1, col2 而非 SELECT *
4. 釋放資源：$result->free() / $stmt->close() / $pdo = null
5. 錯誤模式：mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) 或 PDO::ERRMODE_EXCEPTION
6. 批量插入：單條 SQL 多值 VALUES (...), (...), ...，事務包裹大批量
7. 時間存儲：推薦 DATETIME / TIMESTAMP，或應用層統一用 Unix 時間戳 INT