How to Add and Use the Levenshtein Distance Function in MySQL?

Adding the Levenshtein Distance Function to MySQL

To incorporate the Levenshtein distance function into MySQL, navigate to the MySQL Workbench and execute the following statement:

CREATE FUNCTION levenshtein(s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE len1 INT DEFAULT LENGTH(s1);
  DECLARE len2 INT DEFAULT LENGTH(s2);
  DECLARE i, j, cost, temp INT DEFAULT 0;
  DECLARE c1, c2 CHAR(1);

  IF len1 = 0 THEN
    RETURN len2;
  ELSEIF len2 = 0 THEN
    RETURN len1;
  END IF;

  SET cost = ARRAY(len1 + 1, len2 + 1);

  FOR i = 1 TO len1 DO
    SET cost[i][0] = i;
  END FOR;

  FOR j = 1 TO len2 DO
    SET cost[0][j] = j;
  END FOR;

  FOR i = 1 TO len1 DO
    c1 = SUBSTR(s1, i, 1);
    FOR j = 1 TO len2 DO
      c2 = SUBSTR(s2, j, 1);

      IF c1 = c2 THEN
        SET temp = 0;
      ELSE
        SET temp = 1;
      END IF;

      SET cost[i][j] = LEAST(cost[i - 1][j] + 1, cost[i][j - 1] + 1, cost[i - 1][j - 1] + temp);
    END FOR;
  END FOR;

  RETURN cost[len1][len2];
END;

This will create the levenshtein() function in your MySQL instance.

Example Usage in PHP

To use the function in PHP, connect to your MySQL database and execute a query using the levenshtein() function:

<?php

$servername = "localhost";
$database = "test";
$username = "root";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Execute query
$result = $conn->query("SELECT levenshtein('abcde', 'abced')");

// Get result
$row = $result->fetch_assoc();

// Print result
echo $row['levenshtein(s1, s2)'];

?>

In this example, the query returns 2, indicating the Levenshtein distance between "abcde" and "abced" is 2.

The above is the detailed content of How to Add and Use the Levenshtein Distance Function in MySQL?. For more information, please follow other related articles on the PHP Chinese website!