MySQL 交易機制與資料交換實戰：從 ACID 特性到 secure_file_priv 安全防護的完整指南

-- MySQL 交易基礎操作範例
-- 展示如何使用交易保護資料更新操作

-- 啟動新的交易
-- START TRANSACTION 與 BEGIN 功能相同，可以互換使用
-- 從這個時間點開始，所有的資料修改都在交易範圍內
START TRANSACTION;

-- 執行資料更新操作
-- 更新 inventory 資料表中的顏色欄位
-- 只針對製造商為 Ford 且型號為 Focus 的記錄
UPDATE inventory
SET color = 'blue'              -- 將顏色更新為藍色
WHERE mfg = 'Ford'              -- 製造商條件
  AND model = 'Focus';          -- 型號條件

-- 驗證更新結果
-- 查詢剛才更新的記錄，確認是否符合預期
SELECT * FROM inventory
WHERE mfg = 'Ford' AND model = 'Focus';

-- 此時有兩種選擇：

-- 選擇 1：若結果正確，提交交易
-- COMMIT 將所有修改永久保存到資料庫
-- 一旦提交就無法撤銷
COMMIT;

-- 選擇 2：若結果錯誤，回復交易
-- ROLLBACK 撤銷所有在交易中的修改
-- 資料庫回復到 START TRANSACTION 之前的狀態
-- ROLLBACK;

-- 注意事項：
-- 1. 只有 DML 語句（INSERT、UPDATE、DELETE）可以被回復
-- 2. DDL 語句（CREATE、DROP、ALTER）會自動提交當前交易
-- 3. 交易期間會鎖定相關的資料列，影響並發性能
-- 4. 長時間未提交的交易會占用資源，應盡快決定提交或回復
-- 5. 某些儲存引擎（如 MyISAM）不支援交易，只有 InnoDB 支援

#!/bin/bash
# MySQL 憑證安全管理範例
# 使用 mysql_config_editor 工具儲存加密憑證

# 設定資料庫連線憑證
# 這個命令會啟動互動式介面要求輸入密碼
# 密碼會以加密方式儲存在 ~/.mylogin.cnf 檔案中
mysql_config_editor set \
  --login-path=production \     # 登入配置名稱，可以設定多組
  --host=db.example.com \       # 資料庫伺服器位址
  --user=admin \                # 資料庫使用者名稱
  --password                    # 會提示輸入密碼

# 系統會顯示提示訊息：
# Enter password: [輸入密碼]
# 密碼輸入時不會顯示任何字元，這是正常的安全機制

# 查看已儲存的憑證資訊
# 密碼會以星號顯示，不會顯示明文
mysql_config_editor print --all

# 輸出範例：
# [production]
# user = admin
# password = *****
# host = db.example.com

# 使用儲存的憑證連接資料庫
# --login-path 參數指定要使用的憑證配置
mysql --login-path=production -D inventory

# 無需輸入密碼就能直接連接
# 這個方式特別適合自動化腳本

# 移除特定的登入配置
# mysql_config_editor remove --login-path=production

# 重置所有配置
# mysql_config_editor reset

# 設定多組憑證的範例
# 可以為不同環境設定不同的連線資訊

# 開發環境
mysql_config_editor set \
  --login-path=dev \
  --host=localhost \
  --user=dev_user \
  --password

# 測試環境
mysql_config_editor set \
  --login-path=test \
  --host=test-db.example.com \
  --user=test_user \
  --password

# 生產環境
mysql_config_editor set \
  --login-path=prod \
  --host=prod-db.example.com \
  --user=prod_user \
  --password

# 使用時指定對應的環境
mysql --login-path=dev -D myapp
mysql --login-path=test -D myapp
mysql --login-path=prod -D myapp

# 安全注意事項：
# 1. .mylogin.cnf 檔案權限應設為 600（只有擁有者可讀寫）
# 2. 不要將 .mylogin.cnf 加入版本控制系統
# 3. 在共享伺服器上使用時要特別小心
# 4. 定期更新密碼並重新設定憑證
# 5. 使用不同的 login-path 區分不同環境與權限等級

-- MySQL 資料載入範例
-- 展示如何從檔案批次載入資料

-- 基本的 CSV 檔案載入
-- 載入逗號分隔的資料檔案
LOAD DATA LOCAL INFILE '/path/to/market_indexes.csv'
INTO TABLE market_index
FIELDS TERMINATED BY ','        -- 欄位以逗號分隔
LINES TERMINATED BY '\n'        -- 每列資料以換行符號結束
IGNORE 1 LINES;                 -- 跳過第一列的欄位標題

-- 載入包含引號的 CSV 檔案
-- 處理欄位值可能包含逗號的情況
LOAD DATA LOCAL INFILE '/path/to/products.csv'
INTO TABLE products
FIELDS 
  TERMINATED BY ','             -- 欄位分隔符號
  ENCLOSED BY '"'               -- 欄位值用雙引號包圍
  ESCAPED BY '\\'               -- 跳脫字元
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

-- 指定欄位對應關係
-- 當資料檔案的欄位順序與資料表不同時
LOAD DATA LOCAL INFILE '/path/to/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(emp_id, first_name, last_name, @hire_date, salary)
SET hire_date = STR_TO_DATE(@hire_date, '%Y-%m-%d');
-- 使用變數 @hire_date 暫存日期字串
-- 透過 SET 子句轉換為正確的日期格式

-- 載入 Tab 分隔的檔案
-- 常見於從試算表軟體匯出的資料
LOAD DATA LOCAL INFILE '/path/to/data.tsv'
INTO TABLE my_table
FIELDS TERMINATED BY '\t'       -- Tab 字元分隔
LINES TERMINATED BY '\n';

-- 處理 Windows 換行符號
-- Windows 檔案使用 CRLF（\r\n）作為換行
LOAD DATA LOCAL INFILE '/path/to/windows_data.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'      -- Windows 換行符號
IGNORE 1 LINES;

-- 只載入部分欄位
-- 資料檔案包含的欄位少於資料表
LOAD DATA LOCAL INFILE '/path/to/partial_data.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(customer_name, email, phone);
-- 只載入這三個欄位
-- 其他欄位使用預設值或 NULL

-- 資料轉換範例
-- 載入時進行資料格式轉換
LOAD DATA LOCAL INFILE '/path/to/sales.csv'
INTO TABLE sales
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(sale_id, product_id, @amount, @sale_date)
SET 
  amount = CAST(@amount AS DECIMAL(10,2)),
  sale_date = STR_TO_DATE(@sale_date, '%m/%d/%Y'),
  created_at = NOW();
-- 金額轉換為精確的十進位數
-- 日期從美式格式轉換為 MySQL 日期格式
-- 自動填入建立時間

-- 啟用本地資料載入
-- 某些 MySQL 配置預設停用 LOCAL 功能
-- 需要先啟用才能載入客戶端檔案
SET GLOBAL local_infile = ON;

-- 檢查是否已啟用
SHOW GLOBAL VARIABLES LIKE 'local_infile';

-- 注意事項：
-- 1. LOCAL 關鍵字表示檔案位於客戶端，省略則表示伺服器端
-- 2. 伺服器端檔案路徑受 secure_file_priv 限制
-- 3. 大檔案載入時建議停用索引，載入完成後重建
-- 4. 載入前備份資料，避免格式錯誤導致資料損壞
-- 5. 使用 IGNORE 或 REPLACE 處理重複鍵值

-- MySQL 資料匯出範例
-- 展示如何將查詢結果匯出到檔案

-- 基本的資料匯出
-- 將整個資料表匯出為 CSV 檔案
SELECT * FROM market_index
INTO OUTFILE '/var/lib/mysql-files/market_index.csv'
FIELDS 
  TERMINATED BY ','             -- 欄位以逗號分隔
  OPTIONALLY ENCLOSED BY '"'    -- 文字欄位用雙引號包圍
LINES TERMINATED BY '\n';       -- 每列以換行符號結束

-- 匯出查詢結果
-- 只匯出符合條件的資料
SELECT emp_id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales'
  AND salary > 50000
INTO OUTFILE '/var/lib/mysql-files/high_salary_sales.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 包含欄位標題的匯出
-- 使用 UNION 將標題列加入結果
SELECT 'emp_id', 'name', 'department', 'salary'
UNION ALL
SELECT emp_id, 
       CONCAT(first_name, ' ', last_name),
       department,
       salary
FROM employees
INTO OUTFILE '/var/lib/mysql-files/employees_with_header.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';

-- 匯出為 Tab 分隔檔案
-- 適合匯入到試算表軟體
SELECT * FROM products
INTO OUTFILE '/var/lib/mysql-files/products.tsv'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

-- 檢查 secure_file_priv 設定
-- 確認允許匯出的目錄
SHOW GLOBAL VARIABLES LIKE 'secure_file_priv';

-- 輸出範例：
-- +------------------+----------------------------+
-- | Variable_name    | Value                      |
-- +------------------+----------------------------+
-- | secure_file_priv | /var/lib/mysql-files/      |
-- +------------------+----------------------------+

-- 若 secure_file_priv 為 NULL
-- 表示不允許使用 LOAD DATA 和 SELECT INTO OUTFILE

-- 若 secure_file_priv 為空字串
-- 表示沒有目錄限制（不建議，安全風險高）

-- 匯出時的注意事項：
-- 1. 檔案不能已存在，MySQL 不會覆蓋現有檔案
-- 2. MySQL 程序必須有目標目錄的寫入權限
-- 3. 匯出路徑必須是絕對路徑
-- 4. 只能匯出到 secure_file_priv 指定的目錄
-- 5. 大量資料匯出時注意磁碟空間