MySQL 常用 SQL 語句速查手冊：匯入匯出、備份恢復、DDL/DML、進階查詢技巧

前言

MySQL 日常運維與開發中，高頻 SQL 語句掌握度直接決定效率。本文彙整 資料匯入匯出 (mysqldump/LOAD DATA/SELECT INTO OUTFILE)、備份恢復策略、DDL/DML 標準語法、進階查詢技巧 (分頁、去重、字串聚合、條件聚合)、常用函數與型態，並針對 Windows/Linux 路徑差異、權限問題、編碼坑點特別標註，作為桌邊速查手冊。

一、資料匯出

1. mysqldump 邏輯備份 (標準工具)

2. SELECT INTO OUTFILE (伺服器端檔案，高效能)

-- CSV 格式 (Excel 相容)
SELECT * FROM orders
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

-- 制表符分隔 (預設)
SELECT id, name, email FROM users
INTO OUTFILE '/var/lib/mysql-files/users.txt';

-- 指定欄位順序與函數
SELECT id, UPPER(name), DATE_FORMAT(created_at, '%Y-%m-%d')
INTO OUTFILE '/var/lib/mysql-files/users_fmt.txt'
FIELDS TERMINATED BY '|' FROM users;

權限要求：需 FILE 權限，輸出目錄需 secure_file_priv 允許 (查詢 SHOW VARIABLES LIKE 'secure_file_priv';)。檔案**不能預先存在**，MySQL 不會覆蓋。

3. mysqlpump (8.0+ 並行備份工具)

mysqlpump -uroot -p --parallel-schemas=4 --compress-output=ZLIB db_name > dump.gz

二、資料匯入

1. 還原 mysqldump 檔案

# 交互式密碼
mysql -uroot -p db_name < backup.sql

# 指定字符集 (防亂碼)
mysql -uroot -p --default-character-set=utf8mb4 db_name < backup.sql

# 大檔案優化：關閉自動提交、唯一檢查、外鍵檢查
mysql -uroot -p -e "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; SOURCE backup.sql; COMMIT; SET unique_checks=1; SET foreign_key_checks=1;" db_name

2. LOAD DATA INFILE (高效大量載入)

-- 基本用法
LOAD DATA INFILE '/var/lib/mysql-files/users.txt'
INTO TABLE users
FIELDS TERMINATED BY '|' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES  -- 跳過標題行
(id, name, email, @created_str)
SET created_at = STR_TO_DATE(@created_str, '%Y-%m-%d %H:%i:%s');

-- 本地檔案 (客戶端讀取)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

關鍵參數：
IGNORE 1 LINES 跳過標題
FIELDS TERMINATED BY 分隔符
ENCLOSED BY 包圍字符 (處理含分隔符的欄位)
SET col = expr 欄位轉換/賦值，支援用戶變數 @var
REPLACE / IGNORE 處理主鍵/唯一鍵衝突

3. mysqlimport (命令列封裝 LOAD DATA)

mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --ignore-lines=1 --local -uroot -p db_name users.csv

三、備份與恢復策略

物理備份 (極速恢復)

• Percona Xtrabackup：熱備份、增量、壓縮、加密、並行 (見 Xtrabackup 指南)。
• 檔案系統快照：LVM/ZFS/AWS EBS 快照，需 FLUSH TABLES WITH READ LOCK 瞬間一致性 (秒級)。
• MySQL Enterprise Backup：官方商業工具。

邏輯備份 (跨版本/跨平台/審計)

• mysqldump / mysqlpump
• 適用：遷移升級、異構同步、部分資料導出、審計存證。

備份保留策略範例

四、DDL/DML 標準語法速查

建表範本 (生產級)

CREATE TABLE `orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
  `order_no` CHAR(20) NOT NULL COMMENT '業務訂單號',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0待付款 1已付款 2已發貨 3已完成 4已取消',
  `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '訂單金額(分)',
  `currency` CHAR(3) NOT NULL DEFAULT 'TWD' COMMENT '幣別',
  `remark` VARCHAR(500) DEFAULT NULL COMMENT '備註',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `deleted_at` TIMESTAMP NULL DEFAULT NULL COMMENT '軟刪除時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_status` (`user_id`, `status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='訂單表';

常用 ALTER 範例

-- 新增欄位
ALTER TABLE orders ADD COLUMN `pay_time` TIMESTAMP NULL COMMENT '支付時間' AFTER `status`;

-- 修改欄位型態 (大表慎用，需 pt-osc)
ALTER TABLE orders MODIFY COLUMN `total_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00;

-- 新增索引
ALTER TABLE orders ADD INDEX `idx_pay_time` (`pay_time`);

-- 刪除索引
ALTER TABLE orders DROP INDEX `idx_pay_time`;

-- 重命名表
RENAME TABLE orders TO orders_2024_archive;

五、進階查詢技巧

1. 分頁查詢優化

-- 深分頁優化：延遲關聯 (需 id 連續或有索引)
SELECT t.* FROM (SELECT id FROM orders WHERE user_id=123 ORDER BY id LIMIT 100000, 20) d
JOIN orders t ON t.id = d.id;

-- 游標分頁 (前端傳 last_id)
SELECT * FROM orders WHERE user_id=123 AND id > 987654 ORDER BY id LIMIT 20;

2. 去重刪除 (保留最小 id)

DELETE t1 FROM users t1
JOIN users t2 ON t1.email = t2.email AND t1.id > t2.id;

3. 字串聚合 (GROUP_CONCAT)

-- 將同群組名稱用逗號串聯
SELECT dept_id, GROUP_CONCAT(name ORDER BY id SEPARATOR ',') AS names
FROM users GROUP BY dept_id;

-- 長度限制 (預設 1024，可調大)
SET SESSION group_concat_max_len = 1000000;

4. 條件聚合 (單掃描多指標)

SELECT 
  COUNT(*) AS total,
  SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) AS paid_cnt,
  SUM(CASE WHEN status=1 THEN total_amount ELSE 0 END) AS paid_amt,
  AVG(CASE WHEN status=1 THEN total_amount END) AS paid_avg
FROM orders WHERE user_id=123;

5. 側向衍生表 / LATERAL JOIN (8.0.14+)

SELECT u.*, recent_orders.*
FROM users u
JOIN LATERAL (
  SELECT * FROM orders o WHERE o.user_id = u.id ORDER BY o.created_at DESC LIMIT 3
) recent_orders ON TRUE
WHERE u.status = 1;

6. 遞歸 CTE (8.0+)

WITH RECURSIVE org_tree AS (
  SELECT id, name, parent_id, 1 AS level FROM departments WHERE parent_id = 0
  UNION ALL
  SELECT d.id, d.name, d.parent_id, ot.level + 1
  FROM departments d JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree;

六、常用資料型態對照表

七、權限與安全常用語句

-- 建立唯讀帳號
CREATE USER 'readonly'@'%' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT ON prod_db.* TO 'readonly'@'%';

-- 建立應用帳號 (讀寫特定表)
CREATE USER 'app_order'@'10.0.1.%' IDENTIFIED BY 'StrongPass456!';
GRANT SELECT,INSERT,UPDATE,DELETE ON prod_db.orders TO 'app_order'@'10.0.1.%';
GRANT SELECT ON prod_db.users TO 'app_order'@'10.0.1.%';

-- 審計權限
SHOW GRANTS FOR 'app_order'@'10.0.1.%';

-- 撤銷權限
REVOKE DELETE ON prod_db.orders FROM 'app_order'@'10.0.1.%';

八、Windows/Linux 路徑與編碼注意事項

• 路徑分隔符：Windows C:\data\backup.sql 或 C:/data/backup.sql；Linux /data/backup.sql。
• secure_file_priv：Windows 預設為空 (限制目錄)；Linux 常設為 /var/lib/mysql-files/。
• 字符集一致性：建表 CHARSET=utf8mb4、連線 SET NAMES utf8mb4、匯入 --default-character-set=utf8mb4、匯出 --default-character-set=utf8mb4。
• 換行符：Windows \r\n，Linux \n，LOAD DATA 需指定 LINES TERMINATED BY。