MySQLREGEXP以任何順序檢查字串是否包含所有這些字符

我正在嘗試撰寫一個搜索單詞串列的查詢。其中一個條件是檢查包含（以任何順序）字串中的任何字符的單詞。

例如，單詞必須以任意順序包含“o”和“d”，因此“ABDOMEN”和“ABOUND”都是正確的。

我的查詢是：

SELECT word
FROM words
WHERE lower(word) like 'ab%'                   /* Words starts with AB               */
AND   REGEXP_INSTR(lower(word), '[str]') = 0   /* does not contain any of r, s or t  */
AND   REGEXP_INSTR(lower(word), '[od]') > 0    /* must  contain both o and d         */

問題是“必須包含”條件，特別是讓它檢查“O”和“D”，而上面它更像是“O”或“D”。

實驗，我發現這有效：

AND   REGEXP_INSTR(lower(word), '(o.*d|d.*o)' ) > 0    /* must  contain both o and d         */

問題是我必須（在 PHP 中）(o.*d|d.*o)從原始od. 當串列超過 3 個字符時，生成這變得很困難。

另一種選擇是為“必須包含”串列中的每個字符添加單獨的條件：

AND   INSTR(lower(word), 'o' ) > 0    /* must  contain o          */
AND   INSTR(lower(word), 'd' ) > 0    /* must  contain d         */

但是，當bind_param在 PHP 中使用呼叫時，傳遞這些會使代碼變得混亂。

MySQL 中是否有“單線”可以實作上述目標？

uj5u.com熱心網友回復：

區分大小寫由列上的排序規則決定。除非您有使用區分大小寫排序規則的特定原因，否則我建議將其更改為不區分大小寫以消除顯式強制大小寫的需要。無需對每個單詞應用另一個函式，您只需將 REGEXP_INSTR 的匹配型別設定為不區分大小寫即可。您還可以將前綴檢查移動到正則運算式中 -

SELECT word
FROM words
WHERE REGEXP_INSTR(word, '(?=^ab)(?=.*o)(?=.*d)', 1, 1, 0, 'i');

當然，上面的查詢不能使用任何可用的索引來過濾，所以將前綴移動到正則運算式并不是一個好主意。這導致我做一些測驗。我拿了字典的簡化副本來創建下表（111,745 行）-

CREATE TABLE `words` (
  `word_cs` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL,
  `word_ci` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  KEY `idx_word_ci` (`word_ci`),
  KEY `idx_word_cs` (`word_cs`)
) ENGINE=InnoDB;

我針對區分大小寫運行的第一批查詢，word_cs每個查詢運行了五次，時間是平均值 -

SELECT word_cs
FROM words
WHERE REGEXP_INSTR(word_cs, '(?=^ab)(?=.*o)(?=.*d)', 1, 1, 0, 'i');
/* Returned 58 rows; Examined 111745 rows; Serverside execution time: 0.166s */

SELECT word_cs
FROM words
WHERE REGEXP_INSTR(lower(word_cs), '(?=^ab)(?=.*o)(?=.*d)');
/* Returned 58 rows; Examined 111745 rows; Serverside execution time: 0.193s */

SELECT word_cs
FROM words
WHERE lower(word_cs) LIKE 'ab%'
AND REGEXP_INSTR(word_cs, '(?=.*o)(?=.*d)', 1, 1, 0, 'i');
/* Returned 58 rows; Examined 111745 rows; Serverside execution time: 0.067s */

SELECT word_cs
FROM words
WHERE lower(word_cs) LIKE 'ab%'
AND REGEXP_INSTR(lower(word_cs), '(?=.*o)(?=.*d)');
/* Returned 58 rows; Examined 111745 rows; Serverside execution time: 0.065s */

SELECT word_cs
FROM words
WHERE lower(word_cs) LIKE 'ab%'
AND INSTR(lower(word_cs), 'o' ) > 0
AND INSTR(lower(word_cs), 'd' ) > 0;
/* Returned 58 rows; Examined 111745 rows; Serverside execution time: 0.064s */

SELECT word_cs
FROM words
WHERE lower(word_cs) LIKE 'ab%'
AND lower(word_cs) LIKE '%o%'
AND lower(word_cs) LIKE '%d%';
/* Returned 58 rows; Examined 111745 rows; Serverside execution time: 0.063s */

然后，我對不區分大小寫的查詢運行了類似的（由于不區分大小寫而略有修改）批次word_ci-

SELECT word_ci
FROM words
WHERE REGEXP_INSTR(word_ci, '(?=^ab)(?=.*o)(?=.*d)', 1, 1, 0, 'i');
/* Returned 58 rows; Examined 111745 rows; Serverside execution time: 0.147s */

SELECT word_ci
FROM words
WHERE REGEXP_INSTR(word_ci, '(?=^ab)(?=.*o)(?=.*d)');
/* Returned 58 rows; Examined 111745 rows; Serverside execution time: 0.157s */

SELECT word_ci
FROM words
WHERE word_ci LIKE 'ab%'
AND REGEXP_INSTR(word_ci, '(?=.*o)(?=.*d)', 1, 1, 0, 'i');
/* Returned 58 rows; Examined 525 rows; Serverside execution time: 0.003s */

SELECT word_ci
FROM words
WHERE word_ci LIKE 'ab%'
AND REGEXP_INSTR(word_ci, '(?=.*o)(?=.*d)');
/* Returned 58 rows; Examined 525 rows; Serverside execution time: 0.003s */

SELECT word_ci
FROM words
WHERE word_ci LIKE 'ab%'
AND INSTR(word_ci, 'o' ) > 0
AND INSTR(word_ci, 'd' ) > 0;
/* Returned 58 rows; Examined 525 rows; Serverside execution time: 0.001s */

SELECT word_ci
FROM words
WHERE word_ci LIKE 'ab%'
AND word_ci LIKE '%o%'
AND word_ci LIKE '%d%';
/* Returned 58 rows; Examined 525 rows; Serverside execution time: 0.001s */

性能上的真正差異不在于排序規則的大小寫敏感性或缺乏這種敏感性，而在于查詢是否可搜索。在列值上使用 LOWER() 會否定索引的使用。

/* Full table scan */
SELECT word_cs
FROM words
WHERE lower(word_cs) LIKE 'ab%'

/* Uses index if available */
SELECT word_cs
FROM words
WHERE word_cs LIKE 'ab%'
OR word_cs LIKE 'AB%'
OR word_cs LIKE 'Ab%'
OR word_cs LIKE 'aB%';

uj5u.com熱心網友回復：

這一系列字母可以像本例那樣處理，它們的出現順序將被忽略：

WHERE REGEXP_INSTR(lower(word), '(?=.*O)(?=.*D)')

標籤：

上一篇：獲取給定日期串列的多個類別的值總和

下一篇：MySQL似乎更新速度很慢