MySQL函数用于检查两个文本之间的相似百分比

SELECT LEVENSHTEIN_RATIO('hello', 'world') AS similarity_ratio;

DELIMITER //
CREATE FUNCTION LEVENSHTEIN_RATIO(s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS INT
BEGIN
    DECLARE s1_len, s2_len, max_len, cost, i, j, tmp INT;
    DECLARE s1_arr, s2_arr CHAR(255);
    SET s1_len = LENGTH(s1), s2_len = LENGTH(s2), max_len = GREATEST(s1_len, s2_len), cost = 0;
    SET s1_arr = UNHEX(HEX(s1)), s2_arr = UNHEX(HEX(s2));
    IF s1_len = 0 THEN
        RETURN s2_len;
    ELSEIF s2_len = 0 THEN
        RETURN s1_len;
    ELSE
        FOR i = 0 TO s1_len DO
            SET tmp = 0;
            FOR j = 0 TO s2_len DO
                IF i = 0 THEN
                    SET tmp = tmp + 1;
                ELSIF j = 0 THEN
                    SET tmp = tmp + 1;
                ELSIF SUBSTRING(s1_arr, i, 1) = SUBSTRING(s2_arr, j, 1) THEN
                    SET tmp = tmp + 0;
                ELSE
                    SET tmp = tmp + 1;
                END IF;
                IF i > 0 AND j > 0 THEN
                    SET cost = LEAST(cost + 1, tmp - SUBSTRING(s1_arr, i - 1, 1) = SUBSTRING(s2_arr, j - 1, 1) + 1, tmp - SUBSTRING(s2_arr, j - 1, 1) = SUBSTRING(s1_arr, i - 1, 1) + 1);
                END IF;
            END FOR;
            SET tmp = cost;
        END FOR;
        RETURN ROUND((1 - (cost / (2 * max_len))) * 100);
    END IF;
END //
DELIMITER ;