MySQL 開發必知的 8 個實用 SQL 技巧：批次插入、自增 ID、NULL 處理、表修復與日期運算

技巧一：跨表批次插入 (單條 SQL、原子操作)

-- 同結構表全量複製
INSERT INTO target_db.users SELECT * FROM source_db.users;

-- 指定欄位、帶條件、轉換
INSERT INTO archive_orders (id, user_id, amount, created_at, status)
SELECT id, user_id, amount, created_at, 'ARCHIVED'
FROM orders 
WHERE created_at < '2023-01-01' AND status = 'COMPLETED';

技巧二：多值 INSERT 批次寫入 (效能提升 10-50 倍)

-- 應用層組裝 (每批 500-1000 行)
INSERT INTO logs (level, message, created_at) VALUES
('INFO', 'User login', '2024-01-15 10:00:00'),
('WARN', 'Slow query detected', '2024-01-15 10:00:01'),
('ERROR', 'Connection timeout', '2024-01-15 10:00:02'),
...;

-- 配合 ON DUPLICATE KEY UPDATE 實現 Upsert
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (1001, 1, NOW())
ON DUPLICATE KEY UPDATE 
    login_count = login_count + 1,
    last_login = VALUES(last_login);

技巧三：獲取剛插入的自增 ID (連線隔離、線程安全)

-- 1. 會話級函數 (推薦，不受並發影響)
INSERT INTO users (name) VALUES ('John');
SELECT LAST_INSERT_ID();  -- 返回當前連線最後生成的 AUTO_INCREMENT 值

-- 2. 多行插入返回首個 ID
INSERT INTO users (name) VALUES ('A'), ('B'), ('C');
SELECT LAST_INSERT_ID();  -- 返回 'A' 的 ID，後續 ID = 首個 ID + 1, +2...

-- 3. 存儲函數/觸發器內部使用
CREATE FUNCTION create_user(p_name VARCHAR(50)) RETURNS BIGINT
BEGIN
    INSERT INTO users (name) VALUES (p_name);
    RETURN LAST_INSERT_ID();
END;

-- ❌ 錯誤用法：SELECT MAX(id) FROM users (並發下極不準確)

技巧四：NULL 值優雅處理 - COALESCE / IFNULL / NULLIF

-- 返回第一個非 NULL 值 (標準 SQL，多參數)
SELECT COALESCE(nickname, username, 'Guest') AS display_name FROM users;

-- 兩參數簡寫 (MySQL 專用)
SELECT IFNULL(phone, '未填寫') FROM users;

-- 相等則返回 NULL (避免除零、統一空字串為 NULL)
SELECT NULLIF(trial_days, 0) FROM subscriptions;  -- 0 → NULL
UPDATE users SET email = NULLIF(TRIM(email), ''); -- 空串 → NULL

-- 進階：NULL 排序控制
SELECT * FROM tasks ORDER BY 
    CASE WHEN priority IS NULL THEN 1 ELSE 0 END,  -- NULL 最後
    priority DESC;

技巧五：損壞表修復 (MyISAM 專用、InnoDB 不適用)

-- 1. 檢查表狀態
CHECK TABLE myisam_table;

-- 2. 修復 (REPAIR 會重建索引、修復資料檔)
REPAIR TABLE myisam_table;

-- 3. myisamchk 離線修復 (更徹底，需停機)
myisamchk -r /var/lib/mysql/db/table.MYI  # 修復
myisamchk -r -q /var/lib/mysql/db/table.MYI  # 快速修復 (僅索引)

-- ⚠️ InnoDB 損壞處理：
-- 1. innodb_force_recovery=1..6 啟動讀取
-- 2. mysqldump 導出 → 重建表 → 導入
-- 3. 或使用 Percona Data Recovery Tool

技巧六：毫秒級時間戳轉換 (FROM_UNIXTIME 陷阱)

-- 陷阱：FROM_UNIXTIME() 參數為秒，傳入毫秒會溢位 → NULL/錯誤日期
SELECT FROM_UNIXTIME(1705312800123);  -- NULL (超過 2038 年)

-- 正確：除以 1000 取秒部分
SELECT FROM_UNIXTIME(1705312800123 / 1000);  -- 2024-01-15 10:00:00

-- 保留毫秒精度 (MySQL 5.6.4+ DATETIME(3)/TIMESTAMP(3))
SELECT TIMESTAMPADD(MICROSECOND, 1705312800123 % 1000 * 1000, 
       FROM_UNIXTIME(1705312800123 / 1000)) AS dt_ms;
-- 2024-01-15 10:00:00.123

技巧七：日期加減運算 - DATE_ADD / DATE_SUB / INTERVAL

-- 基礎語法
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);      -- 明天
SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH);    -- 3 個月前
SELECT DATE_ADD('2024-01-15', INTERVAL 2 HOUR);  -- 2 小時後

-- 支援單位：MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
-- 組合單位：DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND

-- 實戰：本月第一天/最後一天
SELECT DATE_FORMAT(NOW(), '%Y-%m-01') AS month_start;
SELECT LAST_DAY(NOW()) AS month_end;

-- 實戰：本週一 (模式 1=週一開始)
SELECT DATE_SUB(NOW(), INTERVAL (WEEKDAY(NOW())) DAY);

技巧八：子查詢陷阱與優化

-- ❌ 低效：不相關子查詢在 WHERE 中逐行執行
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 'ACTIVE');

-- ✅ 高效：JOIN (或 8.0+ 半連接優化自動轉換)
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'ACTIVE';

-- ✅ 高效：EXISTS (半連接，滿足即停止)
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 'ACTIVE');

-- ⚠️ 子查詢必須加 LIMIT 1 (標量子查詢返回多行報錯)
SELECT (SELECT name FROM users WHERE id = o.user_id LIMIT 1) AS user_name
FROM orders o;

-- 衍生表 (FROM 子查詢) 必須加別名
SELECT * FROM (SELECT * FROM users WHERE status = 'ACTIVE') AS u;  -- 正確

總結速查卡