函數定義與基本用法

ABS(X)  -- 返回 X 的絕對值
-- 等價於：IF(X < 0, -X, X)

基礎示例

SELECT ABS(2);     -- 2
SELECT ABS(-32);   -- 32
SELECT ABS(0);     -- 0
SELECT ABS(-3.14); -- 3.14

核心應用場景：查找最接近目標值的記錄

業務場景

表 `sum_baseline` 存儲基準時間 `TotalTime`，給定目標值 84，查找**差值絕對值最小**的記錄。

標準寫法

SELECT 
    ABS(TotalTime - 84) AS diff,
    `RankTotaltime`
FROM `sum_baseline`
WHERE Activitytime = '2013-08-20 00:00:00'
ORDER BY diff ASC
LIMIT 1;

進階：返回原值而非差值

SELECT TotalTime, `RankTotaltime`
FROM `sum_baseline`
WHERE Activitytime = '2013-08-20 00:00:00'
ORDER BY ABS(TotalTime - 84) ASC
LIMIT 1;

性能優化：避免全表掃描

問題

`ORDER BY ABS(col - val)` **無法使用索引**，導致全表掃描 + filesort。

優化方案一：範圍查詢 + 索引（已知大致範圍）

-- 假設目標值 84，先查找附近範圍
SELECT TotalTime, `RankTotaltime`
FROM `sum_baseline`
WHERE Activitytime = '2013-08-20 00:00:00'
  AND TotalTime BETWEEN 70 AND 98  -- 經驗範圍 ±14
ORDER BY ABS(TotalTime - 84) ASC
LIMIT 1;

優化方案二：虛擬列 + 索引（MySQL 5.7+ / 8.0）

-- 添加虛擬列存儲絕對差值（需固定目標值場景）
ALTER TABLE sum_baseline 
ADD COLUMN diff_84 INT AS (ABS(TotalTime - 84)) VIRTUAL,
ADD INDEX idx_diff_84 (diff_84);

-- 查詢秒級
SELECT TotalTime, `RankTotaltime`
FROM sum_baseline
WHERE Activitytime = '2013-08-20 00:00:00'
ORDER BY diff_84 ASC
LIMIT 1;

優化方案三：應用層計算 + 索引範圍

應用層計算目標範圍，推送給 SQL：

-- 應用層計算
target = 84
range = 20
sql = "SELECT * FROM t WHERE time BETWEEN ? AND ? ORDER BY ABS(time - ?) LIMIT 1"
params = [target-range, target+range, target]

其他實用場景

1. 數據清洗：修正負數庫存/金額

UPDATE inventory SET quantity = ABS(quantity) WHERE quantity < 0;

2. 差異報表：絕對偏差統計

SELECT 
    AVG(ABS(actual - predicted)) AS mae,
    MAX(ABS(actual - predicted)) AS max_dev
FROM predictions;

3. 排序去重：按絕對值排序

SELECT DISTINCT ABS(value) FROM measurements ORDER BY 1;

類型系統注意事項

• 整數參數返回整數，小數參數返回小數
• `ABS(-9223372036854775808)` 溢出（超出 BIGINT 有符號範圍），返回負數，需用 `CAST(... AS DECIMAL)` 避免
• NULL 輸入返回 NULL

總結

`ABS()` 是簡單強大的數學函數，**核心價值在於將「差值大小」轉化為可排序、可比較的非負指標**。最常見高頻場景是「找最接近值」，性能關鍵在於**避免全表 filesort**，通過**範圍索引、虛擬列索引、應用層範圍推送**三大手段實現毫秒級查詢。業務代碼中善用 `ABS()` 可大幅簡化邏輯判斷分支。