AI で MySQL のテキストを分類する

rows = db.query("SELECT id, body FROM support_tickets WHERE category IS NULL")
for row in rows:
    category = llm.classify(row["body"], labels=["billing", "technical", "shipping", "other"])
    db.execute("UPDATE support_tickets SET category = ? WHERE id = ?", category, row["id"])

INSTALL EXTENSION vsql_ai;

SET @key = 'sk-ant-your-api-key';

ALTER TABLE support_tickets ADD COLUMN category VARCHAR(20);

-- max_execution_time only bounds SELECT statements in MySQL/VillageSQL —
-- it has no effect on INSERT/UPDATE/DELETE, including this UPDATE. To
-- bound how long a batch UPDATE can run, use KILL QUERY against the
-- connection's own id from a timer, not a session variable set beforehand.

UPDATE support_tickets
SET category = ai_prompt(
    'anthropic',
    'claude-haiku-4-5',
    @key,
    CONCAT(
        'Classify this support ticket into exactly one of these categories: ',
        'billing, technical, shipping, other. ',
        'Reply with the category name only, lowercase, no punctuation.\n\n',
        'Ticket: ', body
    )
)
WHERE category IS NULL
LIMIT 100;

-- Check what labels the model returned
SELECT DISTINCT category, COUNT(*) AS cnt
FROM support_tickets
GROUP BY category
ORDER BY cnt DESC;

-- Fix off-label results
UPDATE support_tickets
SET category = 'other'
WHERE category NOT IN ('billing', 'technical', 'shipping', 'other');

ALTER TABLE articles ADD COLUMN topics JSON;

UPDATE articles
SET topics = ai_prompt(
    'anthropic',
    'claude-haiku-4-5',
    @key,
    CONCAT(
        'Return a JSON array of topics that apply to this article. ',
        'Choose from: ["technology", "business", "health", "science", "politics"]. ',
        'Return only the JSON array, no other text.\n\n',
        'Article: ', content
    )
)
WHERE topics IS NULL
LIMIT 50;

-- Query rows tagged with a specific topic
SELECT title FROM articles
WHERE JSON_CONTAINS(topics, '"technology"');

ALTER TABLE reviews ADD COLUMN ai_result JSON;

UPDATE reviews
SET ai_result = ai_prompt(
    'anthropic',
    'claude-haiku-4-5',
    @key,
    CONCAT(
        'Classify this review. Return JSON with keys "label" (positive/negative/neutral) ',
        'and "confidence" (high/medium/low). Return only valid JSON.\n\n',
        'Review: ', review_text
    )
)
WHERE ai_result IS NULL
LIMIT 100;

-- Query by label and confidence
SELECT review_text,
       JSON_UNQUOTE(JSON_EXTRACT(ai_result, '$.label')) AS label,
       JSON_UNQUOTE(JSON_EXTRACT(ai_result, '$.confidence')) AS confidence
FROM reviews
WHERE JSON_UNQUOTE(JSON_EXTRACT(ai_result, '$.label')) = 'negative'
  AND JSON_UNQUOTE(JSON_EXTRACT(ai_result, '$.confidence')) = 'high';