反向索引 mysql

CREATE TABLE documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT
);

INSERT INTO documents (content) VALUES
('This is a sample document'),
('Another document with some text'),
('Yet another document for testing');

CREATE TABLE inverted_index (
    word VARCHAR(255) PRIMARY KEY,
    doc_id INT,
    FOREIGN KEY (doc_id) REFERENCES documents(id)
);

DELIMITER $$
CREATE PROCEDURE create_inverted_index()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_word VARCHAR(255);
    DECLARE cur_doc_id INT;
    DECLARE cur_pos INT;
    DECLARE cur_doc TEXT;
    DECLARE cur_index INT DEFAULT 0;

    DECLARE cur CURSOR FOR SELECT id, content FROM documents;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO cur_doc_id, cur_doc;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET cur_pos = 1;
        WHILE cur_pos <= LENGTH(cur_doc) DO
            SET cur_word = SUBSTRING_INDEX(SUBSTRING_INDEX(cur_doc, ' ', cur_pos), ' ', -1);
            IF cur_word <> '' THEN
                INSERT INTO inverted_index (word, doc_id) VALUES (cur_word, cur_doc_id)
                ON DUPLICATE KEY UPDATE doc_id = doc_id;
            END IF;
            SET cur_pos = cur_pos + LENGTH(cur_word) + 1;
        END WHILE;
    END LOOP;

    CLOSE cur;
END$$
DELIMITER ;

CALL create_inverted_index();