MySQL 數據插入與查詢完全指南：命令行、PHP 腳本與最佳實踐

INSERT 插入數據核心語法

-- 標準語法
INSERT INTO table_name (col1, col2, col3) VALUES (val1, val2, val3);

-- 批量插入（高效）
INSERT INTO users (name, email) VALUES 
    ('Alice', 'alice@test.com'),
    ('Bob', 'bob@test.com'),
    ('Carol', 'carol@test.com');

-- 查詢結果插入
INSERT INTO archive_users SELECT * FROM users WHERE created_at < '2023-01-01';

-- 忽略重複鍵
INSERT IGNORE INTO users (id, name) VALUES (1, 'Test');

-- 衝突更新
INSERT INTO users (id, name, cnt) VALUES (1, 'Test', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

命令行交互式插入實戰

mysql -u root -p
USE testdb;
INSERT INTO tutorials (title, author, created_at) VALUES 
    ('Learn PHP', 'John', NOW()),
    ('Learn MySQL', 'Abdul', NOW()),
    ('Java Tutorial', 'Sanjay', '2023-05-06');

要點：

• 字符串必須用單引號 'val' 或雙引號 "val"
• NOW() 返回當前 datetime，自增列無需指定
• 箭頭 -> 為提示符換行標識，非 SQL 一部分

PHP 腳本插入數據（mysqli 預處理）

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

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

// 預處理防注入
$stmt = $mysqli->prepare('INSERT INTO tutorials (title, author, created_at) 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();
?>

安全要點：

1. 使用 prepare() + bind_param() 完全防 SQL 注入
2. 檢查 magic_quotes_gpc 避免雙重轉義（PHP 5.4+ 已移除）
3. 輸入驗證：長度、格式、類型、業務規則

SELECT 查詢核心語法

-- 基礎查詢
SELECT col1, col2 FROM table WHERE condition ORDER BY col2 DESC LIMIT 10;

-- 聚合統計
SELECT COUNT(*), SUM(amount), AVG(price), MAX(date), MIN(id) FROM orders;

-- 分組聚合
SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price 
FROM products GROUP BY category HAVING cnt > 5;

-- 多表連接
SELECT u.name, o.order_no, o.amount 
FROM users u LEFT JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'paid';

命令行查詢與輸出控制

mysql -u root -p -e "SELECT * FROM tutorials LIMIT 5;" testdb
# 垂直顯示
mysql -u root -p -e "SELECT * FROM tutorials LIMIT 1\G" testdb
# 導出 CSV
mysql -u root -p --batch --raw -e "SELECT * FROM tutorials" testdb | sed 's/\t/,/g' > out.csv

PHP 腳本查詢數據（完整流程）

<?php
$mysqli = new mysqli('localhost', 'root', 'pass', 'testdb');
$sql = 'SELECT id, title, author, created_at FROM tutorials ORDER BY created_at DESC LIMIT 20';
$result = $mysqli->query($sql);

if ($result) {
    // 關聯數組遍歷
    while ($row = $result->fetch_assoc()) {
        echo "ID: {$row['id']}
";
        echo "Title: {$row['title']}
";
        echo "Author: {$row['author']}
";
        echo "Date: {$row['created_at']}
";
    }
    $result->free(); // 釋放結果集內存
} else {
    echo 'Query failed: ' . $mysqli->error;
}
$mysqli->close();
?>

fetch 方法對比：

性能優化關鍵點

• 避免 SELECT *：明確列出所需列，減少網絡傳輸、利用覆蓋索引
• LIMIT 分頁優化：深分頁改用 WHERE id > last_id LIMIT 10 (游標分頁)
• 釋放結果集：$result->free() 或 mysqli_free_result() 防止內存洩漏
• 預處理複用：重複執行同結構 SQL 時準備一次、綁定多次執行
• 批量操作：單條 INSERT 合併為批次，或用 LOAD DATA INFILE 導入大數據