MySQL でのベクトル埋め込みの生成

INSTALL EXTENSION vsql_ai;

SET @key = 'your-openai-api-key';

SELECT ai_embedding('openai', 'text-embedding-3-small', @key, 'Machine learning is fascinating');
-- Returns: [0.02646778, 0.019067757, -0.05332306, ...]

-- On 0.0.6 and earlier, this throws an error — JSON_LENGTH rejects binary charset
-- ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'
SELECT JSON_LENGTH(ai_embedding('openai', 'text-embedding-3-small', @key, 'Machine learning'));

-- This works on every version
SELECT JSON_LENGTH(
  CONVERT(ai_embedding('openai', 'text-embedding-3-small', @key, 'Machine learning')
          USING utf8mb4)
);

CREATE TABLE documents (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    title    VARCHAR(255),
    content  TEXT,
    embedding JSON
);

SET @key = 'your-openai-api-key';
-- max_execution_time only bounds SELECT statements — it has no effect on
-- this INSERT.

INSERT INTO documents (title, content, embedding) VALUES
    ('Intro to ML',
     'Machine learning is a subset of artificial intelligence',
     CONVERT(ai_embedding('openai', 'text-embedding-3-small', @key,
                          'Machine learning is a subset of artificial intelligence')
             USING utf8mb4)),
    ('Neural Networks',
     'Deep learning uses multi-layer neural networks',
     CONVERT(ai_embedding('openai', 'text-embedding-3-small', @key,
                          'Deep learning uses multi-layer neural networks')
             USING utf8mb4));

-- Confirm the vectors parsed as JSON
SELECT id, JSON_LENGTH(embedding) AS dimensions FROM documents;

ALTER TABLE documents ADD COLUMN embedding JSON;

SET @key = 'your-openai-api-key';
-- max_execution_time only bounds SELECT statements — it has no effect on
-- this UPDATE. To cap how long it runs, get SELECT CONNECTION_ID() beforehand
-- and run KILL QUERY <id> from another session if it runs too long.

UPDATE documents
SET embedding = CONVERT(ai_embedding('openai', 'text-embedding-3-small', @key, content)
                        USING utf8mb4)
WHERE embedding IS NULL
LIMIT 50;  -- Process in batches

-- Generate a query embedding
SET @query_embedding = ai_embedding('openai', 'text-embedding-3-small', @key, 'neural networks');

-- Retrieve stored embeddings to process in application code
SELECT id, title, embedding FROM documents;

-- Embed the full content for document search
SET embedding = CONVERT(ai_embedding('openai', 'text-embedding-3-small', @key, content)
                        USING utf8mb4);

-- Embed a specific field for product search
SET embedding = CONVERT(ai_embedding('openai', 'text-embedding-3-small', @key, product_description)
                        USING utf8mb4);

-- Embed concatenated fields for richer context
SET embedding = CONVERT(ai_embedding('openai', 'text-embedding-3-small', @key,
                            CONCAT(product_name, '. ', category, '. ', product_description))
                        USING utf8mb4);