MySQL 基礎概念

什麼是資料庫與 MySQL

簡介

在現代資訊系統中，資料庫 (Database) 是用來永久保存、管理與檢索資料的核心技術。無論是電商網站、行動應用，或是企業內部的 ERP 系統，都離不開資料庫的支撐。選擇合適的資料庫類型與管理系統，直接影響系統的效能、可擴充性與維護成本。

MySQL 是目前最廣泛使用的開源關聯式資料庫管理系統 (RDBMS)。它在大小企業、雲端服務與開源社群中都有大量的使用案例。了解 MySQL 的基本概念與操作，能讓開發者在設計資料模型、撰寫查詢語句、以及進行效能調校時，事半功倍。

---

核心概念

1. 資料庫與資料表

• 資料庫：一個邏輯容器，用來存放多個相關的資料表、檢視 (view)、索引 (index) 等物件。
• 資料表 (Table)：由行 (row) 與欄 (column) 構成的二維結構，每一列代表一筆紀錄，每一欄代表屬性。

舉例：在電商系統中，shop_db 可能包含 users、products、orders 三張資料表。

2. SQL 基本語法

SQL（Structured Query Language）是與關聯式資料庫互動的標準語言，主要分為四大類：

• DDL（Data Definition Language）：建立、修改、刪除結構，如 CREATE、ALTER、DROP。
• DML（Data Manipulation Language）：操作資料，如 SELECT、INSERT、UPDATE、DELETE。
• DCL（Data Control Language）：權限管理，如 GRANT、REVOKE。
• TCL（Transaction Control Language）：交易控制，如 COMMIT、ROLLBACK、SAVEPOINT。

3. MySQL 伺服器與客戶端

• MySQL Server：負責資料的儲存、索引管理、查詢執行與交易處理。
• MySQL Client：開發者常用的工具，如 mysql 命令列、MySQL Workbench、或程式語言的驅動程式 (PDO、MySQLi、Connector/J)。

4. 資料型別與索引

• 資料型別：MySQL 提供數值、字串、日期時間、二進位等多種型別，選擇正確的型別能降低儲存空間與提升查詢效能。
• 索引：透過 B‑Tree、Hash 等結構加速搜尋。常見索引有 PRIMARY KEY、UNIQUE、INDEX、以及全文索引 (FULLTEXT)。

---

程式碼範例

以下範例均使用 SQL，在說明時以 -- 作為註解符號。

1. 建立資料庫與資料表

-- 建立一個名為 shop_db 的資料庫，使用 UTF8 編碼
CREATE DATABASE IF NOT EXISTS shop_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 切換到 shop_db
USE shop_db;

-- 建立 users 資料表，設定主鍵與唯一索引
CREATE TABLE IF NOT EXISTS users (
    user_id   INT UNSIGNED AUTO_INCREMENT,
    username  VARCHAR(50) NOT NULL,
    email     VARCHAR(100) NOT NULL,
    password  VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    UNIQUE KEY uq_email (email)
) ENGINE=InnoDB;

說明：

• utf8mb4 能完整支援 Unicode（含 Emoji）。
• InnoDB 引擎支援交易與外鍵。

2. 插入、查詢與更新資料

-- 新增一筆使用者資料
INSERT INTO users (username, email, password)
VALUES ('alice', 'alice@example.com', SHA2('P@ssw0rd', 256));

-- 查詢所有使用者的基本資訊
SELECT user_id, username, email, created_at
FROM users
WHERE email LIKE '%@example.com%';

-- 更新使用者的密碼
UPDATE users
SET password = SHA2('N3wP@ss', 256)
WHERE user_id = 1;

說明：

• SHA2() 為簡易的雜湊加密，實務上建議使用 bcrypt 或 argon2。
• LIKE 搭配 % 可做通配字元搜尋。

3. 建立關聯與交易 (Transaction)

-- 建立 products 與 orders 兩張表，並設定外鍵關聯
CREATE TABLE products (
    product_id   INT UNSIGNED AUTO_INCREMENT,
    name         VARCHAR(100) NOT NULL,
    price        DECIMAL(10,2) NOT NULL,
    stock        INT UNSIGNED DEFAULT 0,
    PRIMARY KEY (product_id)
) ENGINE=InnoDB;

CREATE TABLE orders (
    order_id     INT UNSIGNED AUTO_INCREMENT,
    user_id      INT UNSIGNED NOT NULL,
    product_id   INT UNSIGNED NOT NULL,
    qty          INT UNSIGNED NOT NULL,
    total_price  DECIMAL(10,2) NOT NULL,
    order_date   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id)    REFERENCES users(user_id)    ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- 使用交易確保庫存與訂單同步
START TRANSACTION;

-- 假設使用者 1 購買 product_id=3, 數量 2
UPDATE products SET stock = stock - 2 WHERE product_id = 3 AND stock >= 2;
INSERT INTO orders (user_id, product_id, qty, total_price)
VALUES (1, 3, 2, (SELECT price FROM products WHERE product_id = 3) * 2);

COMMIT;   -- 若任一步驟失敗則使用 ROLLBACK

說明：

• START TRANSACTION、COMMIT、ROLLBACK 確保 原子性，避免庫存不一致。
• ON DELETE CASCADE 會在使用者被刪除時，同步刪除其所有訂單。

4. 建立索引提升查詢效能

-- 為 orders 的 order_date 欄位建立索引，常用於時間範圍查詢
CREATE INDEX idx_order_date ON orders (order_date);

-- 使用索引加速查詢最近 7 天的訂單
SELECT *
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 7 DAY);

說明：

• 為頻繁查詢的欄位建立索引，可將全表掃描 (Full Table Scan) 改為索引搜尋，大幅降低 I/O。

5. 使用視圖簡化複雜查詢

-- 建立一個結合使用者與訂單資訊的視圖
CREATE VIEW v_user_orders AS
SELECT u.user_id, u.username, o.order_id, o.total_price, o.order_date
FROM users u
JOIN orders o ON u.user_id = o.user_id;

-- 直接從視圖查詢特定使用者的購買紀錄
SELECT *
FROM v_user_orders
WHERE username = 'alice';

說明：

• 視圖 (View) 可將複雜的 JOIN 抽象化，讓前端程式碼更易讀。

---

常見陷阱與最佳實踐

常見陷阱	為何會發生	推薦的最佳實踐
忘記設定字符編碼	預設使用 latin1，導致中文顯示為亂碼	建立資料庫與資料表時明確指定 utf8mb4，並在連線字串加上 charset=utf8mb4
直接使用 SELECT *	會把不必要的欄位也讀出，浪費記憶體與 I/O	只選取實際需要的欄位，或使用投影 (Projection)
缺乏索引	大資料表查詢變慢，甚至導致超時	分析慢查詢 (EXPLAIN) 後，對 WHERE、JOIN、ORDER BY 欄位建立合適的索引
未使用交易	多步驟操作失敗後資料不一致	針對需要原子性的操作，務必使用 START TRANSACTION … COMMIT/ROLLBACK
過度正規化	產生過多的 JOIN，查詢成本升高	在性能與維護之間取得平衡，適度使用反正規化或資料快取
硬編碼密碼	程式碼泄漏導致安全風險	使用環境變數或密碼管理服務，並在資料庫層面設定最小權限的使用者帳號

---

實際應用場景

1. 部落格平台

   • 使用 posts、comments、tags 三張表，透過 外鍵 與 索引 快速呈現文章列表與評論。
   • 以 全文索引 (FULLTEXT) 支援關鍵字搜尋。

2. 即時聊天系統

   • 以 InnoDB 的行級鎖 (row‑level lock) 處理高併發寫入，並搭配 分區表 (partitioning) 儲存歷史訊息。

3. 電商網站

   • 商品庫存、訂單、使用者資料皆以 交易 保證一致性。
   • 透過 視圖 為報表系統提供即時統計，減少重複撰寫複雜 JOIN。

4. 資料分析與 BI

   • 使用 MySQL 8.0 的 Window Functions 產生月度銷售排行，並將結果匯出至 BI 工具 (如 Power BI)。

---

總結

• 資料庫 是資訊系統的基石，而 MySQL 以其開源、易學、效能佳的特性，成為入門與實務開發的首選。
• 了解 資料庫與資料表的概念、SQL 基本語法、索引與交易，能讓開發者在設計系統時避免常見的效能與一致性問題。
• 透過本篇提供的 程式碼範例，你可以快速上手創建資料庫、操作資料、建立關聯與交易，並掌握索引與視圖的使用技巧。
• 最後，別忘了 遵循最佳實踐：正確設定編碼、適度索引、使用交易、以及最小權限原則，才能在實務環境中建構出可靠、可擴充的 MySQL 應用。

祝你在 MySQL 的學習旅程中，從「什麼是資料庫」踏出堅實的第一步，持續探索更進階的優化與架構設計！