{"id":17028,"date":"2026-10-05T09:57:05","date_gmt":"2026-10-05T16:57:05","guid":{"rendered":"https:\/\/www.pingcap.com\/?post_type=article&p=17028"},"modified":"2026-10-05T10:01:19","modified_gmt":"2026-10-05T17:01:19","slug":"in-age-of-llm-how-to-store-vectors-with-mysql-sql-grammar","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/in-age-of-llm-how-to-store-vectors-with-mysql-sql-grammar\/","title":{"rendered":"In Age of LLM: How to Store Vectors with MySQL SQL Grammar"},"content":{"rendered":"\n

Adding vector search to an application usually means adopting a new client library and a new query language on top of the SQL your team already knows. TiDB<\/a> takes a different approach. Vector storage and search use familiar MySQL syntax, extended with a native VECTOR data type and a small set of distance functions. If you can write a MySQL query, you only need to learn those additions to store and search vectors in TiDB.<\/p>\n\n\n\n

<\/span>How TiDB Solves the Second-Query-Language Problem<\/strong><\/span><\/h2>\n\n\n\n

A dedicated vector database usually ships its own client library and query syntax, separate from the SQL interface your application already speaks. TiDB avoids that split by extending its SQL parser and type system with a native VECTOR data type and distance functions such as VEC_COSINE_DISTANCE. Vectors travel over the standard MySQL protocol as ordinary string literals, so existing MySQL drivers connect without changes. ORMs get vector support through lightweight integration packages rather than a separate vector database SDK.<\/p>\n\n\n\n

The vector type and functions are TiDB extensions, not part of MySQL itself. Everything around them is SQL your team already writes: CREATE TABLE, INSERT, SELECT, ORDER BY, and LIMIT.<\/p>\n\n\n\n

Dify<\/a>, the open-source LLM app development platform, consolidated nearly half a million database containers into a single TiDB Cloud deployment. It now runs vector search, knowledge graphs, and operational data in one system.<\/p>\n\n\n\n

<\/span>Setting Up TiDB for Vector Search<\/strong><\/span><\/h2>\n\n\n\n

Vector search is available on TiDB Cloud Starter, TiDB Cloud Essential, TiDB Cloud Dedicated, and TiDB Self-Managed. On Dedicated and Self-Managed, the cluster must run TiDB v8.4.0 or later, and v8.5.0 or later is recommended.<\/p>\n\n\n\n

The quickest path is TiDB Cloud Starter. Create a cluster, connect with any MySQL client or driver, and start creating vector columns right away. No separate client library is required.<\/p>\n\n\n\n

<\/span>Creating and Querying Vector Data in TiDB<\/strong><\/span><\/h2>\n\n\n\n

Create a table with a vector column the same way you would create any other table, specifying a dimension that matches your embedding model:<\/p>\n\n\n\n

CREATE TABLE vector_table (
    id INT PRIMARY KEY,
    doc TEXT,
    embedding VECTOR(1536)
);<\/code><\/pre>\n\n\n\n
Insert rows the same way you would insert any other row, passing the embedding as a vector literal:<\/p>\n\n\n\n
-- Vectors are truncated for readability.
-- Supply all 1536 values, or the insert fails on a dimension mismatch.
INSERT INTO vector_table (id, doc, embedding) VALUES
    (1, 'TiDB is a distributed SQL database.', '[0.01, 0.02, 0.03, ...]'),
    (2, 'TiFlash is the columnar storage engine.', '[0.04, 0.02, 0.01, ...]'),
    (3, 'TiKV is the row-based storage engine.', '[0.02, 0.05, 0.01, ...]');<\/code><\/pre>\n\n\n\n
Query by similarity by ordering on VEC_COSINE_DISTANCE and limiting the result count:<\/p>\n\n\n\n
SELECT id, doc, VEC_COSINE_DISTANCE(embedding, '[0.01, 0.02, 0.03, ...]') AS distance
FROM vector_table
ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.01, 0.02, 0.03, ...]')
LIMIT 3;<\/code><\/pre>\n\n\n\n
Without an index, this query compares the query vector against every row. Results are exact, but the query slows down as the table grows. For larger tables, add an HNSW index on the vector column:<\/p>\n\n\n\n
CREATE TABLE vector_table_indexed (
    id INT PRIMARY KEY,
    doc TEXT,
    embedding VECTOR(1536),
    VECTOR INDEX idx_embedding ((VEC_COSINE_DISTANCE(embedding)))
);<\/code><\/pre>\n\n\n\n
The vector index runs on TiFlash, TiDB’s columnar engine. Defining the index in CREATE TABLE, as above, creates the TiFlash replica automatically. To add an index to an existing table that has no TiFlash replica, create the replica first with ALTER TABLE vector_table SET TIFLASH REPLICA 1; and then run CREATE VECTOR INDEX.<\/p>\n\n\n\n
The optimizer uses the index only when the query orders by the same distance function the index was built with, in ascending order, with a LIMIT clause. Vector indexes currently support cosine and L2 distance. A WHERE filter applied before the distance sort prevents index use, so run the vector search in a subquery and filter its results instead.<\/p>\n\n\n\n
Index results are approximate. TiDB’s documentation reports recall generally above 90%, which suits most retrieval workloads but can differ from the exact results of an unindexed query.<\/p>\n\n\n\n
Apart from the VECTOR type, the VEC_ functions, and the VECTOR INDEX clause, every statement here is SQL a MySQL developer already writes.<\/p>\n\n\n\n
<\/span>FAQ<\/strong><\/span><\/h2>\n\n\n\n
Do I need a separate vector database to use TiDB’s VECTOR type?<\/strong><\/p>\n\n\n\n