更新 FTS 索引列返回“InnoDB FTS Doc ID 無效”

環境：

• Ubuntu 12.04（和 13.04）
• MySQL 5.6.11

我有一個表，上面有一個全文索引（真實表有更多的列和行）：

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
 FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 id INT NOT NULL ,
 title VARCHAR(200),
 body TEXT,
 UNIQUE KEY (FTS_DOC_ID)
) ENGINE=InnoDB;
CREATE FULLTEXT INDEX idx on articles (title);

INSERT INTO articles(id,title,body) 
 VALUES (9, 'MySQL Tutorial','DBMS stands for DataBase ...');

MySQL 文件建議創建 FTS_DOC_ID（使用正確的語法）以防止全表重建。

到目前為止一切都很好，我可以MATCH...AGAINST使用 FTS 索引進行查詢。但是，當我需要更新索引列時：

UPDATE articles set title = 'New MySQL Tutorial'  WHERE id=9;

我得到一個：

Error code 182, SQL state HY000: Invalid InnoDB FTS Doc ID

如果我像這樣手動處理此列：

UPDATE articles a1, (SELECT MAX(FTS_DOC_ID)+1 AS ftsid FROM articles) a2
set title = 'New MySQL Tutorial', FTS_DOC_ID=ftsid  WHERE id=9;

然後更新完成。但這是不可接受的，因為我有幾個並行的程序來更新這個表（儘管所有不同的行）並且風險是ftsid在不同的程序中得到相同的。請注意，更新body未由 FTS 索引索引的列沒有此行為。IE：

UPDATE articles set body = 'Info: DBMS stands for DataBase ...' WHERE id=9;

成功更新數據庫。

這是預期的行為嗎？或者它是一個錯誤？

我在 MySQL錯誤列表中發現了一個關於相反情況的錯誤（無法更新非 fts 索引列，但可以更新 fts 索引列），但不是這種情況。

我注意到的第一件事是該id列沒有被索引。出於所有意圖和目的，您的行為PRIMARY KEY是因為它是唯一沒有定義FTS_DOC_ID的唯一鍵。PRIMARY KEY

由於id沒有索引，我可以看到 mysqld 想要進行全表掃描來完成查詢。id如果您打算擁有兩個不同的唯一鍵並對一行有兩個不同的引用，您也應該想創建一個 UNIQUE KEY 。

請注意，它FTS_DOC_ID具有 auto_increment 屬性。不需要手動增加它，就像你做的那樣

UPDATE articles a1, (SELECT MAX(FTS_DOC_ID)+1 AS ftsid FROM articles) a2
set title = 'New MySQL Tutorial', FTS_DOC_ID=ftsid  WHERE id=9;

事實上，通過這樣做，你實際上是在接受FTS_DOC_ID並改變它id。

回顧您的第一個查詢

UPDATE articles set title = 'New MySQL Tutorial'  WHERE id=9;

為什麼這會是一個問題（本質上是一個錯誤）？同樣，這又回到了id沒有被索引的問題上。

當列沒有被索引時，表的聚集索引（主鍵所在的位置）不知道id. 這是怎麼回事？

根據書

第 12 章第 260,261 頁（副標題二級索引）說明如下：

其他指標呢？它們的結構如何，它們與聚集索引中的數據有什麼關係？

這些附加索引稱為“二級索引”，您可以根據需要為 InnoDB 表創建任意數量的索引。因為我們的範例表模式定義請求在 last_name 列上創建一個索引，所以 InnoDB 在這個值上創建一個二級索引。二級索引中的每個條目都包含來自聚集索引的主鍵值，從而有助於連結回數據。

您的第一個查詢

由於id未編入索引，您的第一次更新失敗。MySQL (eh Oracle) 應該已經發現了這個問題。所以，這是一個錯誤。

您的第二個查詢

該更新有效，因為您通過嘗試手動更新FTS_DOC_ID列間接訪問了聚集索引。

您的第三個查詢

它之所以有效，是因為您根本沒有訪問任何索引，既不是 onid也不是 on body。在那種情況下，全表掃描將很容易滿足這個請求。

結語

如果我是你，我會盡快將其報告為錯誤，因為 MySQL 5.6 的 InnoDB 的 FULLTEXT 索引是當今人們想要的，查詢計劃應該使用 WHERE 子句來適應它，無論是否使用聚集索引訪問。

引用自：https://dba.stackexchange.com/questions/43860