MySQL 視圖建立錯誤 1349 解決方案：子查詢限制與替代寫法

錯誤情境

嘗試建立包含 FROM 子句子查詢的視圖時，MySQL 拋出錯誤：

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

這是 MySQL 的已知限制（參考 Bug #16757），視圖定義中不允許在 FROM 子句使用子查詢（衍生表）。

原始失敗語句

CREATE OR REPLACE VIEW BLOG_V_ADMIN
(ID, NICKNAME, SEX, EMAIL, PHONE, QQ, MSN, HTTP, REGISTER_TIME,
 IP, LAST_LOGIN_IP, LOGIN_TIME)
AS
SELECT
  A.ID, A.NICKNAME, A.SEX, A.EMAIL, A.PHONE, A.QQ, A.MSN, A.HTTP, A.REGISTER_TIME,
  B.IP AS LAST_LOGIN_IP, B.LOGIN_TIME AS LAST_LOGIN_TIME
FROM
  BLOG_ADMIN A
  LEFT JOIN BLOG_ADMIN_LOGIN_TRACK B ON B.ADMIN = A.ID
  LEFT JOIN (
    SELECT ADMIN, MAX(LOGIN_TIME) AS LOGIN_TIME
    FROM BLOG_ADMIN_LOGIN_TRACK
    GROUP BY ADMIN
  ) C ON B.ADMIN = C.ADMIN;

解決策略：改用標量子查詢

將 FROM 子查詢移至 SELECT 清單，改為相關標量子查詢，每欄位單獨取得最新登入時間與 IP。

修正後語句

CREATE OR REPLACE VIEW BLOG_V_ADMIN
(ID, NICKNAME, SEX, EMAIL, PHONE, QQ, MSN, HTTP, REGISTER_TIME,
 LOGIN_TIME, LAST_LOGIN_IP)
AS
SELECT
  A.ID, A.NICKNAME, A.SEX, A.EMAIL, A.PHONE, A.QQ, A.MSN, A.HTTP, A.REGISTER_TIME,
  (SELECT LOGIN_TIME
   FROM BLOG_ADMIN_LOGIN_TRACK
   WHERE ADMIN = A.ID
   ORDER BY LOGIN_TIME DESC LIMIT 1) AS LAST_LOGIN_TIME,
  (SELECT INET_NTOA(IP)
   FROM BLOG_ADMIN_LOGIN_TRACK
   WHERE ADMIN = A.ID
   ORDER BY LOGIN_TIME DESC LIMIT 1) AS LAST_LOGIN_IP
FROM BLOG_ADMIN A;

關鍵說明

• INET_NTOA() 將儲存為整數的 IP 轉換為點分十進位格式。
• 子查詢使用 ORDER BY ... DESC LIMIT 1 取得最新一筆登入記錄。
• 此寫法相容 MySQL 5.0+ 版本，避開視圖定義限制。

替代方案比較