MySQL 資料表別名進階應用：解決自我關聯查詢難題

為什麼需要表別名？

MySQL 早期版本不支援子查詢（4.1 才引入），且舊專案常使用 mysql 擴展而非 mysqli，無法使用新特性。表別名讓同一張表在單一查詢中以不同身份出現多次，完美解決「自我關聯」場景。

經典場景：無限級分類的刪除與遷移

假設分類表 categories：

• cat_id：主鍵
• parent_id：父分類 ID，頂級為 NULL

需求：刪除某分類（$cat_id），其子分類的 parent_id 自動指向被刪除分類的父分類（即「扁平化」一層）。

完整解決方案（含鎖表防併發）

-- 1. 鎖定本表及兩個別名，防止併發修改
LOCK TABLES `categories` WRITE, `categories` AS `c1` WRITE, `categories` AS `c2` WRITE;

-- 2. 更新：將 c1(子分類) 的 parent_id 指向 c2(父分類) 的 parent_id
UPDATE `categories` AS `c1`, `categories` AS `c2`
SET `c1`.`parent_id` = `c2`.`parent_id`
WHERE `c1`.`parent_id` = `c2`.`cat_id`
  AND `c2`.`cat_id` = $cat_id;

-- 3. 刪除目標分類
DELETE FROM `categories` WHERE `cat_id` = $cat_id;

-- 4. 釋放鎖
UNLOCK TABLES;

關鍵細節

• 必須鎖定所有別名：LOCK TABLES 時需列出原表名與所有別名，否則後續 DML 會報錯 Table 'c1' was not locked with LOCK TABLES。
• 別名在 UPDATE 的多表語法中視為獨立表，可正常 JOIN。
• 適用於 MySQL 4.0+，無需子查詢支援。

其他常見用法

• 簡化長表名：SELECT u.name FROM `very_long_user_table_name` AS u
• 同表多次 JOIN：如訂單表同時關聯 buyer、seller 兩個用戶角色。

總結

表別名不只是為了「寫少幾個字」，更是在不支援子查詢或為了效能時，將單表轉化為多表關聯的利器。善用別名 + 鎖表，能優雅解決樹狀結構的遞迴操作難題。