mysql字典树

-- 创建字典树表
CREATE TABLE trie (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parent_id INT,
    char CHAR(1),
    is_end BOOLEAN DEFAULT FALSE
);

-- 插入字符串
DELIMITER $$
CREATE PROCEDURE insert_word(IN word VARCHAR(255))
BEGIN
    DECLARE v_id INT;
    DECLARE v_parent_id INT;
    DECLARE v_length INT;
    SET v_length = LENGTH(word);
    SET v_id = (SELECT id FROM trie WHERE parent_id IS NULL LIMIT 1);
    SET v_parent_id = v_id;
    WHILE v_length > 0 DO
        SET v_length = v_length - 1;
        SET v_id = (SELECT id FROM trie WHERE parent_id = v_parent_id AND char = SUBSTRING(word, v_length + 1, 1));
        IF v_id IS NULL THEN
            INSERT INTO trie (parent_id, char) VALUES (v_parent_id, SUBSTRING(word, v_length + 1, 1));
            SET v_id = LAST_INSERT_ID();
        END IF;
        SET v_parent_id = v_id;
    END WHILE;
    UPDATE trie SET is_end = TRUE WHERE id = v_id;
END$$
DELIMITER ;

-- 查找字符串
DELIMITER $$
CREATE PROCEDURE search_word(IN word VARCHAR(255))
BEGIN
    DECLARE v_id INT;
    DECLARE v_length INT;
    SET v_length = LENGTH(word);
    SET v_id = (SELECT id FROM trie WHERE parent_id IS NULL LIMIT 1);
    WHILE v_length > 0 AND v_id IS NOT NULL DO
        SET v_length = v_length - 1;
        SET v_id = (SELECT id FROM trie WHERE parent_id = v_id AND char = SUBSTRING(word, v_length + 1, 1));
    END WHILE;
    IF v_id IS NOT NULL AND (SELECT is_end FROM trie WHERE id = v_id) = TRUE THEN
        SELECT 'Found' AS result;
    ELSE
        SELECT 'Not Found' AS result;
    END IF;
END$$
DELIMITER ;