MySQL 動態 SQL 導出帶日期時間檔名的資料檔案技巧

MySQL 動態 SQL 導出帶日期時間檔名的資料檔案技巧

需求：純 SQL 導出檔案，檔名包含當前日期時間（如 sql_out_20230701123045.txt）。SELECT ... INTO OUTFILE 不支援變數作為檔名，需結合預處理語句動態構建。

實作步驟

-- 1. 構建動態 SQL 字串，檔名含 DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')
SET @SqlScript = CONCAT(
  "SELECT * INTO OUTFILE '/tmp/sql_out_",
  DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
  ".txt' FROM `user`;"
);

-- 2. 預處理
PREPARE Sql_Text FROM @SqlScript;

-- 3. 執行
EXECUTE Sql_Text;

-- 4. 釋放（可選）
DEALLOCATE PREPARE Sql_Text;

執行結果示例

mysql> SET @SqlScript= CONCAT("SELECT * INTO OUTFILE '/tmp/sql_out_", DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), ".txt' FROM `user`;");
Query OK, 0 rows affected (0.01 sec)

mysql> PREPARE Sql_Text FROM @SqlScript;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE Sql_Text;
Query OK, 33 rows affected (0.00 sec)

關鍵注意事項

• 權限：執行帳號需具備 FILE 權限，且目標目錄（/tmp）需對 MySQL 進程用戶可寫。否則報 Errcode: 13 - Permission denied
• secure_file_priv 限制：MySQL 5.7+ 預設限制導出目錄，檢查 SHOW VARIABLES LIKE 'secure_file_priv';，若非 NULL 僅允許該目錄
• 檔名不可包含路徑遍歷字符，防止注入風險

預處理語法速查

PREPARE stmt_name FROM preparable_sql_statement;  -- 定義
EXECUTE stmt_name [USING @var_name ...];          -- 執行
{DEALLOCATE | DROP} PREPARE stmt_name;            -- 釋放

• preparable_sql_statement 可以是字串字面值或用戶變數
• 單條 SQL 只能包含一條語句
• ? 佔位符僅用於數據值綁定，不可用於表名、欄位名、關鍵字

替代方案

若可使用 Shell/Python 腳本，更靈活：

# Shell 範例
mysqldump -uroot -p db table > "/backup/table_$(date +%Y%m%d%H%M%S).sql"

但純 SQL 方案在無外部腳本權限的受限環境下極具價值。