> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Running AI Models from MySQL Queries

> How to call AI language models directly from MySQL queries using VillageSQL's ai_prompt() function — enriching rows, classifying data, and batch-processing text.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

MySQL doesn't have a way to call an AI model. You process your data in SQL, then move it to application code for any AI step, then bring results back. VillageSQL's `vsql_ai` extension adds `ai_prompt()` — a function that calls an AI model directly from a query, so enrichment, classification, and generation can happen where the data already lives.

## The Problem: Context Switching Between SQL and AI

A typical pattern without VillageSQL:

```python theme={null}
# Pull rows from MySQL
rows = db.query("SELECT id, review_text FROM reviews WHERE ai_label IS NULL")

# Call AI for each row
for row in rows:
    label = openai.chat(model="gpt-4o-mini", prompt=row["review_text"])
    db.execute("UPDATE reviews SET ai_label = ? WHERE id = ?", label, row["id"])
```

This works but it's three round-trips per row: SELECT, AI call, UPDATE. For large tables the coordination overhead adds up, and the enrichment logic lives in application code rather than in the schema where the data lives.

## With VillageSQL: ai\_prompt() in SQL

```sql theme={null}
INSTALL EXTENSION vsql_ai;
```

`ai_prompt(provider, model, api_key, prompt)` makes an API call and returns the response as a string. Use it anywhere a scalar function works.

```sql theme={null}
SET @key = 'sk-ant-your-api-key';

-- Enrich a single row
SELECT review_text,
       ai_prompt('anthropic', 'claude-haiku-4-5', @key,
                 CONCAT('Classify this review as positive, negative, or neutral: ', review_text)
       ) AS sentiment
FROM reviews
WHERE id = 1;
```

### Updating rows in bulk

The most common use: backfill an enrichment column across existing rows.

```sql theme={null}
ALTER TABLE reviews ADD COLUMN ai_label VARCHAR(50);

SET @key = 'sk-ant-your-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 reviews
SET ai_label = ai_prompt(
    'anthropic',
    'claude-haiku-4-5',
    @key,
    CONCAT('Classify as positive, negative, or neutral. Reply with one word only: ', review_text)
)
WHERE ai_label IS NULL
LIMIT 100;  -- Process in batches to stay within rate limits
```

Run the UPDATE in a loop from your application until no more NULL rows remain.

### Using prompt results in WHERE

You can filter on AI output, though this calls the model for every row in the scan:

```sql theme={null}
-- Flag reviews that need human review
SELECT id, review_text FROM reviews
WHERE ai_prompt('anthropic', 'claude-haiku-4-5', @key,
    CONCAT('Does this review mention a refund request? Reply yes or no: ', review_text)
) = 'yes'
LIMIT 20;
```

For performance, prefer updating a stored column and querying that rather than calling `ai_prompt()` in a WHERE clause.

### Building prompts from multiple columns

Concatenate columns into the prompt to give the model full context:

```sql theme={null}
UPDATE support_tickets
SET ai_summary = ai_prompt(
    'anthropic',
    'claude-haiku-4-5',
    @key,
    CONCAT(
        'Summarize this support ticket in one sentence.\n',
        'Subject: ', subject, '\n',
        'Body: ', body
    )
)
WHERE ai_summary IS NULL
LIMIT 50;
```

## Performance and Rate Limits

Each `ai_prompt()` call is an independent HTTPS request. For bulk operations:

* **Use LIMIT in UPDATE** — AI calls take 5–30 seconds each, so a batch `UPDATE` over 100 rows takes minutes; process in batches of 50–100 rows to stay within provider rate limits. `max_execution_time` only bounds `SELECT` and does nothing here — to cap a running `UPDATE`, use `KILL QUERY <connection_id>` from another session.
* **Store results** — Never call `ai_prompt()` in a SELECT without storing the result. Repeated SELECTs re-run the API call each time.

```sql theme={null}
-- Batch loop pattern (run repeatedly until done)
UPDATE reviews
SET ai_label = ai_prompt('anthropic', 'claude-haiku-4-5', @key,
    CONCAT('Label as positive, negative, or neutral. One word: ', review_text))
WHERE ai_label IS NULL
LIMIT 100;

-- Check progress
SELECT COUNT(*) AS remaining FROM reviews WHERE ai_label IS NULL;
```

## Choosing a Model

| Model | Speed | Cost | Use when |
| :- | :- | :- | :- |
| `claude-haiku-4-5` | Fast | Low | Bulk classification, simple labeling |
| `claude-sonnet-5` | Medium | Medium | Complex reasoning, nuanced output |
| `claude-opus-5` | Slow | High | Recommended default — agentic and enterprise work |
| `claude-fable-5` | Slow | High | Most capable — demanding reasoning, long-horizon work |
| `gpt-4o-mini` | Fast | Low | OpenAI ecosystem, simple tasks |
| `gemini-2.5-flash` | Fast | Low | Google ecosystem, balanced tasks |

For classification and labeling tasks, use the fastest/cheapest model that gives acceptable accuracy. Reserve more capable models for tasks where output quality matters more than throughput.

For setup and provider configuration, see [Connecting MySQL to AI APIs](https://villagesql.com/docs/en/guides/ai-api-setup.md).

## Frequently Asked Questions

#### Can I use ai\_prompt() in a DEFAULT expression?

No — non-deterministic functions (which `ai_prompt()` is) can't be used in DEFAULT expressions or generated columns in MySQL.

#### Can I use ai\_prompt() in a stored procedure?

Yes. Stored procedures, triggers, and functions can all call `ai_prompt()`, subject to the same timeout constraints. Be cautious with triggers — an AI call on every INSERT will make every write slow.

#### How do I control the response format?

Through the prompt. "Reply with one word: positive, negative, or neutral" gets predictable output for classification. "Return valid JSON with keys: category, confidence" gets structured output. The model follows instructions reliably when they're specific.

#### Does ai\_prompt() retry on failure?

No. If the API call fails, it returns NULL. Run `SHOW WARNINGS` to see why — `vsql_ai` raises a `Warning 3200` with the real failure reason — before implementing retry logic in your application's batch loop.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `FUNCTION <database>.ai_prompt does not exist` | Run `INSTALL EXTENSION vsql_ai` |
| Returns NULL | Bad API key, rate limit hit, or timeout — check `max_execution_time` |
| Response stops mid-sentence | Run `SHOW WARNINGS` — a "Prompt response truncated to fit the result buffer" warning means the answer did not fit in the result; ask the model for a shorter answer |
| UPDATE runs too long | Use smaller `LIMIT` batches; `max_execution_time` does not apply to `UPDATE`, use `KILL QUERY <connection_id>` from another session instead |
| Model returns unexpected format | Tighten the prompt: specify exact output format and give examples |
| Rate limit errors | Reduce batch size; add `SELECT SLEEP(1);` between UPDATE batches, or add a sleep in your application loop |

## See also

* [Connecting MySQL to AI APIs](https://villagesql.com/docs/en/guides/ai-api-setup.md) — installing and configuring the vsql\_ai extension first
* [AI Summarization in MySQL](https://villagesql.com/docs/en/guides/ai-summarization.md) — summarizing long text with ai\_prompt()
* [Classifying Text in MySQL with AI](https://villagesql.com/docs/en/guides/text-classification.md) — labeling rows by category using ai\_prompt()
* [Sentiment Analysis on MySQL Data](https://villagesql.com/docs/en/guides/sentiment-analysis.md) — scoring text for tone and sentiment


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.