一、資料表設計核心要素
建立表格時，除欄位名稱、型別、屬性外，必須善用約束保障資料完整：
| 約束類型 | 關鍵字 | 用途 |
|---|
| 非空 | NOT NULL | 欄位不可為 NULL |
| 唯一 | UNIQUE | 值不可重複 (允許單一 NULL) |
| 主鍵 | PRIMARY KEY | 唯一識別列，自動 NOT NULL + UNIQUE |
| 外鍵 | FOREIGN KEY | 參照父表主鍵，維護參考完整性 |
| 檢查 | CHECK | 自訂條件 (MySQL 8.0.16+ 強制執行) |
| 自增 | AUTO_INCREMENT | 整數主鍵自動遞增 |
二、DDL 常用語法速查
建立表格
CREATE TABLE `user` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(32) NOT NULL COMMENT '帳號',
`email` VARCHAR(128) NOT NULL UNIQUE COMMENT '信箱',
`age` TINYINT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
修改表格結構
-- 新增欄位
ALTER TABLE `user` ADD `phone` VARCHAR(20) AFTER `email`;
-- 修改欄位型別/屬性
ALTER TABLE `user` MODIFY `age` SMALLINT UNSIGNED NOT NULL DEFAULT 18;
-- 刪除欄位
ALTER TABLE `user` DROP `phone`;
-- 重命名表格
ALTER TABLE `user` RENAME TO `member`;
刪除表格
DROP TABLE IF EXISTS `member`;
三、DML 資料操作實例
新增資料
-- 單筆
INSERT INTO `user` (`username`, `email`, `age`) VALUES ('alice', 'alice@example.com', 25);
-- 多筆 (批次效能佳)
INSERT INTO `user` (`username`, `email`, `age`) VALUES
('bob', 'bob@example.com', 30),
('cindy', 'cindy@example.com', 28);
查詢進階技巧
| 需求 | 語法 | 範例 |
|---|
| 欄位別名 | AS / 空白 | SELECT username AS name FROM user |
| 去重 | DISTINCT | SELECT DISTINCT age FROM user |
| 範圍查詢 | BETWEEN ... AND ... | WHERE age BETWEEN 18 AND 30 |
| 集合查詢 | IN (...) | WHERE username IN ('alice','bob') |
| 模糊比對 | LIKE % _ | WHERE email LIKE '%@gmail.com' |
| 分頁 | LIMIT offset, count | LIMIT 0, 10 |
更新與刪除
UPDATE `user` SET `age` = 26 WHERE `username` = 'alice';
DELETE FROM `user` WHERE `age` < 18;
四、索引分類與建立
| 索引類型 | 關鍵字 | 特性 | 適用場景 |
|---|
| 主鍵索引 | PRIMARY KEY | 唯一、非空、聚簇索引 | 每表必備 |
| 唯一索引 | UNIQUE | 值唯一、允許一個 NULL | Email、身分證字號 |
| 普通索引 | INDEX / KEY | 可重複、可 NULL | 常用 WHERE/JOIN/ORDER BY 欄位 |
| 全文索引 | FULLTEXT | 支援自然語言/布林搜尋 | 文章內容、商品描述 (MySQL 5.6+) |
-- 建表時
INDEX `idx_email` (`email`)
-- 事後新增
ALTER TABLE `user` ADD INDEX `idx_age` (`age`);
ALTER TABLE `user` ADD FULLTEXT `ft_content` (`content`);
五、儲存引擎與字元集設定
查看支援引擎
SHOW ENGINES;
設定預設字元集 (my.ini / my.cnf)
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
注意：utf8mb4 支援完整 Unicode (含 Emoji)，utf8 僅支援 BMP 平面。
連線層級指定字元集
SET NAMES utf8mb4; -- 等同於 SET character_set_client/connection/results = utf8mb4
六、實戰檢核清單
- [ ] 所有表指定
ENGINE=InnoDB、CHARSET=utf8mb4
- [ ] 每表有
PRIMARY KEY (建議 BIGINT UNSIGNED AUTO_INCREMENT)
- [ ] 業務唯一欄位加
UNIQUE 索引
- [ ] 查詢高頻欄位建立
INDEX，避免過度索引
- [ ] 外鍵關係明確定義
ON DELETE/UPDATE 行為
- [ ] 連線程式啟動時執行
SET NAMES utf8mb4
