mysql余弦计算

CREATE TABLE vectors (
    id INT PRIMARY KEY,
    vector1 VARCHAR(255),
    vector2 VARCHAR(255)
);

INSERT INTO vectors (id, vector1, vector2) VALUES
(1, '1,2,3', '4,5,6'),
(2, '2,3,4', '5,6,7');

SELECT 
    id,
    (SUM(a * b) / (SQRT(SUM(a * a)) * SQRT(SUM(b * b)))) AS cosine_similarity
FROM (
    SELECT 
        id,
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(vector1, ',', n), ',', -1) AS FLOAT) AS a,
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(vector2, ',', n), ',', -1) AS FLOAT) AS b
    FROM 
        vectors,
        (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) numbers
    WHERE 
        CHAR_LENGTH(vector1) - CHAR_LENGTH(REPLACE(vector1, ',', '')) >= n - 1
        AND CHAR_LENGTH(vector2) - CHAR_LENGTH(REPLACE(vector2, ',', '')) >= n - 1
) subquery;