表間記錄複製（INSERT ... SELECT）

-- 建表
CREATE TABLE employee (id INT, name CHAR(10), age INT, sex ENUM('M','F'), department CHAR(10));
CREATE TABLE user (uid INT PRIMARY KEY AUTO_INCREMENT, uname CHAR(10), upass CHAR(10));

-- 複製並轉換：小寫用戶名 + 密碼加密
INSERT INTO user (uname, upass)
SELECT LOWER(name), PASSWORD(LOWER(name)) FROM employee;

-- 帶條件複製
INSERT INTO user (uname, upass)
SELECT name, PASSWORD(name) FROM employee WHERE department = 'Finance';

LOWER() 不分大小寫，PASSWORD() 為 MySQL 內部加密函數（注意：5.7+ 棄用，建議應用層用 bcrypt/Argon2）。

資料導出：SELECT ... INTO OUTFILE

SELECT * FROM t20
INTO OUTFILE '/tmp/b.txt'
FIELDS TERMINATED BY ':' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

• 需 FILE 權限
• 目標檔案**不能存在**，目錄需 MySQL 進程寫入權限
• secure_file_priv 限制導出目錄（查詢 SHOW VARIABLES LIKE 'secure_file_priv'）

資料導入：LOAD DATA INFILE

LOAD DATA INFILE '/tmp/a.txt'
INTO TABLE t20
FIELDS TERMINATED BY ':' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

• 同樣受 secure_file_priv 限制
• LOCAL 關鍵字：LOAD DATA LOCAL INFILE 從**客戶端**讀檔，不受服務端 secure_file_priv 限制，但需客戶端/服務端均啟用 local_infile=1
• IGNORE n LINES 跳過標題行
• REPLACE / IGNORE 控制主鍵/唯一鍵衝突行為

實戰技巧

1. 大量導入前：SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 導入後恢復，大幅提速
2. 導出含建表結構：mysqldump -d db table > schema.sql + SELECT ... INTO OUTFILE 純數據
3. 跨表同步：INSERT ... SELECT ... ON DUPLICATE KEY UPDATE ...

權限最小化

GRANT FILE ON *.* TO 'etl_user'@'%';  -- 僅授 FILE 權限
-- 配合 secure_file_priv = '/data/export/' 限制目錄

**核心口訣：INSERT SELECT 做表間複製，INTO OUTFILE/LOAD DATA 做檔案交換，權限目錄鎖緊。**