MariaDB FULLTEXT 搜尋帶撇號和必填詞

撇號/單引號 (’) 應該是全文索引中單詞的一部分。

誠然，我在 MariaDB 網站上找不到這個，但我假設它的行為仍應像 MySQL（5.5 版）。

但是，當使用“IN BOOLEAN MODE”，並在單詞前加上“+”（因此在所有返回的行中都是強制性的）時，不會返回記錄。

例如：

CREATE TABLE customer (
   name TINYTEXT NOT NULL,
   FULLTEXT (name)
) ENGINE = InnoDB;

INSERT INTO customer VALUES ('O''Brien');
INSERT INTO customer VALUES ('O Brien');
INSERT INTO customer VALUES ('X''Brien');
INSERT INTO customer VALUES ('Extra Amy');
INSERT INTO customer VALUES ('Extra Brian');
INSERT INTO customer VALUES ('Extra Cat');
INSERT INTO customer VALUES ('Extra Debbie');

我得到這些結果：

SELECT * FROM customer WHERE MATCH (name) AGAINST ("O'Brien" IN BOOLEAN MODE);
 "O'Brien"
 "O Brien"
 "X'Brien"
3 rows in set (0.000 sec)

SELECT * FROM customer WHERE MATCH (name) AGAINST ("+O'Brien" IN BOOLEAN MODE);
Empty set (0.000 sec)

SELECT * FROM customer WHERE MATCH (name) AGAINST ("+Brien" IN BOOLEAN MODE);
 "O'Brien"
 "O Brien"
 "X'Brien"
3 rows in set (0.000 sec)

SELECT * FROM customer WHERE MATCH (name) AGAINST (+"O'Brien" IN BOOLEAN MODE);
 "O'Brien"
 "O Brien"
 "X'Brien"
3 rows in set (0.000 sec)

我錯過了什麼，比如需要引用撇號嗎？

或者這是 InnoDB 和 MyISAM 之間的故意區別？

---

感謝@rick-james，我更新了這個問題以包含 3 條“額外”記錄以避免 50% 的門檻值限制，並且我在+引用的單詞之前包含了一個範例。

如果我們使用 Aria 或 MyISAM 儲存引擎創建表，則查詢成功：

CREATE TABLE customer2 (
   name TINYTEXT NOT NULL,
   FULLTEXT (name)
) ENGINE = Aria;

INSERT INTO customer2 VALUES ('O''Brien');

SELECT * FROM customer2 WHERE MATCH (name) AGAINST ("+O'Brien" IN BOOLEAN MODE);
1 row in set (0.001 sec)

CREATE TABLE customer3 (
   name TINYTEXT NOT NULL,
   FULLTEXT (name)
) ENGINE = MyISAM;

INSERT INTO customer3 VALUES ('O''Brien');

SELECT * FROM customer3 WHERE MATCH (name) AGAINST ("+O'Brien" IN BOOLEAN MODE);
1 row in set (0.001 sec)

但是，InnoDB 似乎也不適合我（MariaDB 10.4.8。）

所以這讓我認為這是一個錯誤。我在這里報告了這個問題： https ://jira.mariadb.org/browse/MDEV-20797

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