MySQL 實用技巧集錦：顯示資訊、防注入、CHAR 函數與進階技巧

前言

累積實戰經驗中的小技巧能事半功倍。本文整理四大類 MySQL 實用技巧：快速顯示資訊、SQL 注入防禦、CHAR() 字元編碼應用、以及其他進階用法。

技巧一：快速顯示資料庫與資料表資訊

SHOW DATABASES;
SHOW TABLES;
-- 進階：顯示表結構、索引、建表語句
SHOW CREATE TABLE table_name;
SHOW INDEX FROM table_name;
SHOW TABLE STATUS LIKE 'table_name';

技巧二：SQL 執行過程顯示進度/狀態 (PRINT 效果)

SELECT 'Creating Foo table' AS Action;
-- 結果欄位名為 Action，值為 Creating Foo table
-- 適用於大型腳本執行進度追蹤

技巧三：防止 SQL 注入 - mysql_real_escape_string

-- PHP 範例
$safe_input = mysql_real_escape_string($user_input);
-- 或 mysqli
$safe_input = $mysqli->real_escape_string($user_input);

• 過濾單引號 '、雙引號 "、反斜線 \、NUL、換行等危險字元
• 現代最佳實踐： 改用預處理語句 (Prepared Statements)，從根本分離代碼與資料

技巧四：CHAR() 函數 - ASCII 碼組合字串

SELECT CHAR(83, 99, 48, 114, 112, 105, 111, 110) AS Action;
-- 結果: Sc0rtion (對應 ASCII 碼)
-- 應用：繞過關鍵字過濾、動態構造字串、測試編碼

補充進階技巧

1. 快速產生測試資料

-- 產生 100 萬筆測試資料
INSERT INTO test_table (col1, col2)
SELECT a.id, CONCAT('name_', a.id)
FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3) a
JOIN (SELECT 1 AS id UNION SELECT 2 ...) b
JOIN (SELECT 1 AS id UNION SELECT 2 ...) c
JOIN (SELECT 1 AS id UNION SELECT 2 ...) d
JOIN (SELECT 1 AS id UNION SELECT 2 ...) e;  -- 6^5 = 7776 行，再 JOIN 幾次即達百萬

2. 分頁優化 - 書籤式分頁

-- 傳統 LIMIT 深分頁慢
SELECT * FROM t ORDER BY id LIMIT 1000000, 20;

-- 書籤式：記住上頁最大 ID
SELECT * FROM t WHERE id > 999999 ORDER BY id LIMIT 20;

3. 條件更新技巧

-- CASE WHEN 批次更新
UPDATE products SET price = CASE
    WHEN category = 'A' THEN price * 1.1
    WHEN category = 'B' THEN price * 1.05
    ELSE price
END;

4. 查詢執行計畫快速查看

EXPLAIN FORMAT=JSON SELECT * FROM t WHERE col = 'val';
-- JSON 格式更詳細，含成本估算、索引選擇細節

總結

工欲善其事，必先利其器。熟練這些技巧並融入日常開發習慣，能顯著提升 SQL 撰寫效率與安全性。最重要的原則：參數化查詢防注入、EXPLAIN 導向優化、避免深分頁。