如何在 MySQL 中實作 Levenshtein 函數
您希望將 Levenshtein 函數合併到 MySQL 資料庫中以方便字串相似性比較。以下是有關如何實現此目標的逐步指南:
如上所述,您可以在此處取得編輯距離函數的程式碼。取得程式碼後,請繼續執行以下步驟:
使用下列指令建立與您的MySQL 伺服器的連接您首選的方法,例如MySQL Workbench。
執行以下語句以在MySQL 資料庫中建立Levenshtein 函數:
執行以下語句以在MySQL 資料庫中建立Levenshtein 函數:CREATE FUNCTION levenshtein(a VARCHAR(255), b VARCHAR(255)) RETURNS SMALLINT DETERMINISTIC BEGIN DECLARE i, j, s VARCHAR(255), costs VARCHAR(255); DECLARE c, mina, minb, napi, napj, cost_a, cost_b, di, dj, new COSTTYPE[]; SET s = REPEAT(' ',LENGTH(b)+2); SET costs = REPEAT(' ',(LENGTH(b)+2) * (LENGTH(a)+2)); SET c = 1; WHILE c <= LENGTH(a) DO SET new = costs + 1; SET mina = c + 1; SET i = 1; WHILE i <= LENGTH(b) DO SET napj = costs + (i * 2) + 1; SET di = CHAR_LENGTH(SUBSTR(a,c,1)); SET dj = CHAR_LENGTH(SUBSTR(b,i,1)); SET napj = costs + (i * 2) + 2; IF di = dj AND di > 0 AND dj > 0 THEN IF di > 1 AND di < 127 AND dj > 1 AND dj < 127 AND (di-dj=-1 OR di-dj=1) AND SUBSTR(a,c,1) = SUBSTR(b,i,1) THEN SET napi = costs + 2; ELSE SET napi = costs + (i * 2) + 3; END IF; ELSE SET napi = costs + 2; END IF; SET cost_a = napj - mina; SET cost_b = napi - napj; SET mina = new + (i * 2) + 1; SET minb = napi + 1; IF cost_a < cost_b AND cost_a < mina THEN SET c = di + 1; SET costs = new; ELSEIF cost_b < mina THEN SET di = dj + 1; SET costs = new; ELSE SET mina = new + (i * 2) + 1; END IF; SET napj = mina; SET mina = napi; SET i = i + 1; END WHILE; SET c = c + 1; END WHILE; RETURN napj - mina; END;
SHOW FUNCTION STATUS WHERE name = 'levenshtein'
SELECT levenshtein('abcde', 'abced')
$mysqli = new mysqli('localhost', 'username', 'password', 'database'); $query = "SELECT levenshtein('abcde', 'abced')"; $result = $mysqli->query($query); while ($row = $result->fetch_assoc()) { echo $row['levenshtein']; }
以上是如何在MySQL中實作Levenshtein函數?的詳細內容。更多資訊請關注PHP中文網其他相關文章!