> ## 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.

# Generating Vector Embeddings in MySQL

> How to generate and store vector embeddings in MySQL using VillageSQL's ai_embedding() function — for semantic search, similarity matching, and recommendation systems.

> [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 →

Vector embeddings represent text as arrays of numbers that capture semantic meaning. Two pieces of text about the same topic have similar vectors even if they share no words. MySQL has no built-in way to generate embeddings. VillageSQL's `vsql_ai` extension adds `ai_embedding()`, which calls an embedding model and returns the vector as a JSON array you can store and query.

## What Embeddings Are For

An embedding model converts text to a fixed-length vector of floats. The distance between two vectors reflects semantic similarity:

* `"dog"` and `"puppy"` → similar vectors
* `"dog"` and `"database index"` → distant vectors

This enables semantic search (find rows that *mean* the same thing as a query), clustering, and recommendation — none of which keyword search handles well.

## With VillageSQL: ai\_embedding()

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

`ai_embedding(provider, model, api_key, text)` calls an embedding model and returns a JSON array string.

```sql theme={null}
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, ...]
```

### The charset conversion

On VillageSQL 0.0.6 and earlier, `ai_embedding()` returns binary charset, and MySQL's JSON parser rejects a binary string — the result has to pass through `CONVERT(... USING utf8mb4)` before it reaches a `JSON` column or a JSON function:

```sql theme={null}
-- 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)
);
```

VillageSQL 0.0.7 and later return the correct charset directly, so `CONVERT` is no longer required there — but it is harmless to keep, so every example below still uses it for compatibility with 0.0.6 and earlier.

### Storing embeddings

Store the JSON array in a `JSON` column:

```sql theme={null}
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;
```

### Backfilling embeddings on existing rows

```sql theme={null}
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
```

## Choosing a Model

| Provider | Model | Dimensions | Use when |
| :- | :- | :- | :- |
| `'openai'` | `text-embedding-3-small` | 1536 | Best value — good accuracy, lower cost |
| `'openai'` | `text-embedding-3-large` | 3072 | Higher accuracy needed |
| `'google'` | `gemini-embedding-001` | 3072 | Google ecosystem |
| `'local'` | any Ollama model (e.g., `nomic-embed-text`) | varies | Private data; no API key required |

Use the same model for all embeddings in a table. Embeddings from different models are not comparable — mixing them produces meaningless similarity scores.

Anthropic does not currently provide an embedding API.

## Semantic Search

Neither MySQL nor VillageSQL provides a native vector distance function. Full similarity search — finding the nearest vectors in a large table — runs in application code. The typical architecture: store embeddings in MySQL, fetch them, compute cosine similarity in your application, and return the top matches.

```sql theme={null}
-- 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;
```

Pass the results to a vector similarity library (NumPy, scikit-learn, Faiss) to find nearest neighbors. For production-scale search, consider a dedicated vector database alongside MySQL.

## What to Embed

The quality of your embeddings depends on what you embed. Some patterns:

```sql theme={null}
-- 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);
```

Keep the text you embed under a few thousand tokens. Longer text gets truncated by the embedding model.

## Frequently Asked Questions

#### Can I compare embeddings from different models?

No. Embeddings from `text-embedding-3-small` and `gemini-embedding-001` live in different vector spaces — their similarity scores are meaningless when compared against each other. Standardize on one model per column.

#### How much storage do embeddings take?

A JSON array of 1536 floats is roughly 10–15 KB per row as a text string. For large tables, this adds up. Consider whether you need all embeddings stored or just the ones you'll query against.

#### Does ai\_embedding() support batch input?

No — each call processes one text input. For bulk embedding, loop through rows in your application and call `UPDATE` in batches.

#### What happens if the text is too long?

The embedding model truncates input at its token limit (typically 8191 tokens for OpenAI models). The embedding is computed on the truncated text. For long documents, embed a summary or the first few paragraphs rather than the full text.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `FUNCTION <database>.ai_embedding does not exist` | Run `INSTALL EXTENSION vsql_ai` |
| `ERROR 3144: Cannot create a JSON value from a string with CHARACTER SET 'binary'` | Wrap the call in `CONVERT(... USING utf8mb4)` — see [The charset conversion](#the-charset-conversion) |
| `ai_embedding()` returns NULL | Check API key; verify model name; check `max_execution_time` |
| Similarity search returns wrong results | Confirm all embeddings use the same model; check that embeddings weren't generated with different content than what you're searching |
| Embeddings take too long to generate | Use LIMIT batches; embeddings are \~1–3 seconds each per API call |

## See also

* [Connecting MySQL to AI APIs](https://villagesql.com/docs/en/guides/ai-api-setup.md) — installing and configuring the vsql\_ai extension
* [Running AI Models from MySQL Queries](https://villagesql.com/docs/en/guides/ai-prompts-in-mysql.md) — text generation as a complement to embeddings
* [UUIDs in MySQL](https://villagesql.com/docs/en/guides/uuids.md) — stable row identifiers for embedding lookup tables
* [Multi-dimensional range queries](https://villagesql.com/docs/en/guides/cube-queries.md) — geometric containment and distance queries without AI vectors


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