如何辨識 MySQL 中的輔音/元音模式匹配？

晚上好，今天我在處理一個充滿詞彙的 MySQL 表時遇到了一些麻煩。我需要能夠快速檢索具有相同輔音和元音排列的單詞，所以我的想法是沿著這些線建構的，大約有 300k 行：

CREATE TABLE words(
`value` VARCHAR(100),
`pattern` VARCHAR(100)
);

要填充一小部分內容：

INSERT INTO words(`value`) VALUES
 ('aardvark'),
 ('aardvarks'),
 ('aardwolf'),
 ('aardwolves'),
 #...
 ('mortal'),
 ('mortals'),
 #...
 ('posted'),
 ('posteen'),
 #...
 ('zymotoxic'),
 ('zymurgies');

以下查詢應返回“mortal”和“posted”：

SELECT `value` FROM words WHERE `pattern` = '101101';

因為“1”代表輔音，“0”代表元音。我知道 MySQL 沒有 regexp_replace 等效項，所以我想知道填充該pattern欄位的最佳方法是什麼？我假設它不是慢得令人遺憾：

UPDATE words SET `pattern` = REPLACE(`value`, 'a', '0');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'b', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'c', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'd', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'e', '0');
#...
UPDATE words SET `pattern` = REPLACE(`pattern`, 'z', '1');

目前正在嘗試使用游標循環遍歷單詞表並逐個更新每個模式的儲存過程，但也許不出所料，這並沒有更快。我錯過了一些非常明顯的東西嗎？

提前感謝您能給我的任何幫助。

編輯：添加了儲存過程方法。這更快但不是很多（每千行 0.7 秒，加起來），但它完成了工作。

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `set_word_pattern`()
BEGIN
 DECLARE l_last_row INT DEFAULT 0;
 DECLARE temp_word VARCHAR(100);
 #DECLARE temp_char CHAR(1);
 DECLARE temp_pattern_word VARCHAR(100);

 DECLARE c_traverse_words CURSOR FOR SELECT `value` FROM words WHERE pattern = "" LIMIT 20000;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row = 1;

 OPEN c_traverse_words;
 cursor_loop: LOOP
   FETCH c_traverse_words INTO temp_word;
   IF l_last_row = 1 THEN
     LEAVE cursor_loop;
   END IF;
   SET temp_pattern_word = get_pattern(temp_word);
   UPDATE words SET `pattern` = temp_pattern_word WHERE `value` = temp_word;
 END LOOP cursor_loop;
 CLOSE c_traverse_words;

 SELECT "Done"; #temp_pattern_word;
END

以及它呼叫的函式：

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `get_pattern`(new_word VARCHAR(100)) RETURNS varchar(100) CHARSET utf8
BEGIN
 DECLARE temp_char CHAR(1);
 DECLARE temp_pattern_word VARCHAR(100);

 DECLARE i INT;

 SET temp_pattern_word = "";
 SET i = 1;

 WHILE(i <= CHAR_LENGTH(new_word)) DO
   SET temp_char = MID(new_word, i, 1);

   IF temp_char IN ("a", "e", "i", "o", "u") THEN
     SET temp_pattern_word = CONCAT(temp_pattern_word, "0");
   ELSE
     SET temp_pattern_word = CONCAT(temp_pattern_word, "1");
   END IF;
   SET i = i + 1;
 END WHILE;
RETURN temp_pattern_word;
END

一次性完成：

UPDATE ...
   SET pattern = 
       REPLACE(
       REPLACE(
       ...
       REPLACE(value
               'a', '0')
               'b', '1')
               ...
               'z', '1');

你的桌子不需要嗎

PRIMARY KEY(value),
INDEX(pattern)

可能有一種方法可以將這些 0 和 1 轉換為位字元串並將其儲存到一個BIGINT UNSIGNED稍快的查找和更少的磁碟空間中。

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