萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL 條件表達式與字符串函數跨平台指南:CASE WHEN、IIF、SUBSTRING、LEFT 實戰

條件表達式:標準 SQL vs 方言

1. 標準 CASE WHEN(全數據庫通用)

-- 簡單 CASE
CASE col WHEN val1 THEN res1 WHEN val2 THEN res2 ELSE res3 END

-- 搜索 CASE(最靈活)
CASE 
  WHEN col > 100 THEN 'High'
  WHEN col > 50  THEN 'Medium'
  ELSE 'Low'
END AS level

優點:標準、可移植、支持複雜邏輯、可嵌套。
缺點:語法稍冗長。

2. IIF / IF(方言函數)

數據庫函數語法備註
SQL Server / Access / SQLiteIIFIIF(condition, true_val, false_val)三參數,可嵌套
MySQL / MariaDBIFIF(condition, true_val, false_val)三參數,注意:MySQL 還有 IF(expr1, expr2, expr3) 與 IFNULL 混淆
Oracle無原生 IIF用 CASE 或 DECODEDECODE(col, val1, res1, val2, res2, default)
PostgreSQL無 IIF用 CASE9.4+ 支持 NULLIF、COALESCE

建議:跨平台代碼統一用 CASE WHEN,僅在確定單一平台且邏輯極簡時用 IIF/IF。

3. 實戰:條件聚合統計

-- 統計各狀態訂單數(單掃描)
SELECT 
  SUM(CASE WHEN status = 'PAID'   THEN 1 ELSE 0 END) AS paid_cnt,
  SUM(CASE WHEN status = 'REFUND' THEN 1 ELSE 0 END) AS refund_cnt,
  SUM(CASE WHEN status = 'CANCEL' THEN 1 ELSE 0 END) AS cancel_cnt,
  COUNT(*) AS total
FROM orders;

字符串截取函數跨平台對照

功能SQL ServerMySQL / MariaDBPostgreSQLOracle
左起 N 字符LEFT(str, n)LEFT(str, n)LEFT(str, n)SUBSTR(str, 1, n)
右起 N 字符RIGHT(str, n)RIGHT(str, n)RIGHT(str, n)SUBSTR(str, -n)
任意位置截取SUBSTRING(str, start, len)SUBSTRING(str, start, len) / SUBSTRSUBSTRING(str FROM start FOR len)SUBSTR(str, start, len)
字符串長度(字符數)LEN(str) / DATALENGTH(字節)CHAR_LENGTH(str) / LENGTH(str)(字節)LENGTH(str) / CHAR_LENGTH(str)LENGTH(str)(字節) / LENGTHC
查找子串位置CHARINDEX(sub, str, [start])LOCATE(sub, str, [start]) / INSTR(str, sub)POSITION(sub IN str) / STRPOS(str, sub)INSTR(str, sub, [pos], [occurrence])
替換REPLACE(str, old, new)REPLACE(str, old, new)REPLACE(str, old, new)REPLACE(str, old, new)
去除空白LTRIM/RTRIM/TRIMTRIM([[BOTH|LEADING|TRAILING] remstr FROM] str)TRIM/LTRIM/RTRIMTRIM/LTRIM/RTRIM

MySQL 專用:MID = SUBSTRING、LEFT/RIGHT 同標準

SELECT 
  SUBSTRING('Hello World', 7, 5) AS sub1,   -- World
  MID('Hello World', 7, 5) AS mid1,         -- World (別名)
  LEFT('Hello World', 5) AS left1,          -- Hello
  RIGHT('Hello World', 5) AS right1;        -- World

參數詳解:SUBSTRING / LEFT

SUBSTRING ( expression , start , length )

  • expression:字符串、二進制、列、表達式(不可含聚合函數)。
  • start:起始位置(1-based)。負數表示從末尾倒數(MySQL/PostgreSQL 支持,SQL Server 不支持)。
  • length:返回字符數。省略則到字符串末尾。

LEFT ( character_expression , integer_expression )

  • 返回左側指定字符數。
  • integer_expression 為負 → 返回空串(SQL Server);MySQL 返回全串。
  • 返回類型:VARCHAR / NVARCHAR。

實戰技巧:動態截取與清洗

-- 1. 截取第一個 @ 之前的用戶名
SELECT SUBSTRING_INDEX(email, '@', 1) AS username FROM users;  -- MySQL 專用

-- 2. 統一手機號掩碼:138****1234
SELECT CONCAT(LEFT(mobile, 3), '****', RIGHT(mobile, 4)) AS masked FROM users;

-- 3. 解析 JSON 路徑(MySQL 5.7+ / SQL Server 2016+)
SELECT JSON_EXTRACT(data, '$.user.name') AS name;  -- MySQL
SELECT JSON_VALUE(data, '$.user.name') AS name;    -- SQL Server

避坑清單

  • 字符 vs 字節:多字節字符集(utf8mb4)下,LENGTH 返回字節數,CHAR_LENGTH 返回字符數,截取用字符數函數。
  • 隱式轉換:數字隱式轉字符串可能導致格式差異,顯式 CAST(num AS CHAR)。
  • NULL 傳播:任一參數為 NULL,結果多為 NULL,用 COALESCE/IFNULL 預處理。
  • 大字段截取:TEXT/BLOB 列需先 CAST(... AS VARCHAR(MAX)) 或用 SUBSTRING 直接操作(引擎依賴)。

條件表達式用標準 CASE WHEN 走天下;字符串函數記住「LEFT/RIGHT/SUBSTRING 三大金剛」,其餘查手冊。跨平台遷移時,建立函數映射表,單元測試覆蓋所有字符串邏輯。

copyright © 萬盛學電腦網 all rights reserved