polardb/polardbx-sql/polardbx-vector-search

-- 建表时指定维度（推荐）
CREATE TABLE documents (
  id BIGINT PRIMARY KEY,
  content TEXT,
  embedding VECTOR(768)    -- 768 维向量
) PARTITION BY HASH(id) PARTITIONS 4;
 
-- 不指定维度（接受任意长度）
CREATE TABLE flexible_vec (
  id BIGINT PRIMARY KEY,
  vec VECTOR
) PARTITION BY HASH(id) PARTITIONS 4;

-- 插入向量数据
INSERT INTO documents(id, embedding)
VALUES (1, VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4]'));
 
-- 批量插入
INSERT INTO documents(id, content, embedding)
VALUES
  (1, '文档1', VEC_FROMTEXT('[0.1,0.2,0.3,0.4]')),
  (2, '文档2', VEC_FROMTEXT('[0.5,0.6,0.7,0.8]')),
  (3, '文档3', VEC_FROMTEXT('[0.9,0.8,0.7,0.6]'));

-- 查看存储的向量值
SELECT id, VEC_TOTEXT(embedding) FROM documents WHERE id = 1;
-- 返回: [0.1,0.2,0.3,0.4]

SELECT VECTOR_DIM(embedding) FROM documents WHERE id = 1;
-- 返回: 4

-- 需要先有 VECTOR INDEX（定义距离度量）
CREATE VECTOR INDEX vec_idx ON documents(embedding) M=6 DISTANCE=COSINE;
 
-- 计算距离
SELECT id, VEC_DISTANCE(embedding, VEC_FROMTEXT('[0.1,0.2,0.3,0.4]')) AS dist
FROM documents
ORDER BY dist
LIMIT 10;

SELECT id, VEC_DISTANCE_COSINE(embedding, VEC_FROMTEXT('[0.1,0.2,0.3,0.4]')) AS dist
FROM documents
ORDER BY dist
LIMIT 10;

SELECT id, VEC_DISTANCE_EUCLIDEAN(embedding, VEC_FROMTEXT('[0.1,0.2,0.3,0.4]')) AS dist
FROM documents
ORDER BY dist
LIMIT 10;

-- 独立创建（推荐）
CREATE VECTOR INDEX vec_idx ON table_name(column_name) M=6 DISTANCE=COSINE;
 
-- 建表时内联创建
CREATE TABLE documents (
  id BIGINT PRIMARY KEY,
  embedding VECTOR(768),
  VECTOR INDEX vec_idx(embedding) M=6 DISTANCE=COSINE
) PARTITION BY HASH(id) PARTITIONS 4;
 
-- ALTER TABLE 添加
ALTER TABLE documents ADD VECTOR INDEX vec_idx(embedding) M=6 DISTANCE=COSINE;

DROP INDEX vec_idx ON documents;

-- 基本 ANN 搜索：找最相似的 Top-K
SELECT id, content,
  VEC_DISTANCE(embedding, VEC_FROMTEXT('[0.1,0.2,0.3,0.4]')) AS dist
FROM documents
ORDER BY dist
LIMIT 10;
 
-- 结合 WHERE 条件的混合查询
SELECT id, content,
  VEC_DISTANCE(embedding, VEC_FROMTEXT('[0.1,0.2,0.3,0.4]')) AS dist
FROM documents
WHERE category = 'tech'
ORDER BY dist
LIMIT 5;

-- 1. 建表（含向量列和向量索引）
CREATE TABLE knowledge_base (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200),
  content TEXT,
  embedding VECTOR(768),
  VECTOR INDEX emb_idx(embedding) M=16 DISTANCE=COSINE
) PARTITION BY HASH(id) PARTITIONS 8;
 
-- 2. 存入文档 embedding（配合 AI_EMBEDDING 函数生成）
INSERT INTO knowledge_base(title, content, embedding)
VALUES ('PolarDB-X简介', '...内容...', VEC_FROMTEXT(AI_EMBEDDING('PolarDB-X简介...', 'my-embedding')));
 
-- 3. 语义检索
SELECT id, title, content,
  VEC_DISTANCE(embedding, VEC_FROMTEXT(AI_EMBEDDING('什么是分布式数据库', 'my-embedding'))) AS score
FROM knowledge_base
ORDER BY score
LIMIT 5;

-- 存储图片 embedding
CREATE TABLE image_gallery (
  id BIGINT PRIMARY KEY,
  image_url VARCHAR(500),
  embedding VECTOR(1024),
  VECTOR INDEX img_idx(embedding) M=16 DISTANCE=COSINE
) PARTITION BY HASH(id) PARTITIONS 4;
 
-- 以图搜图
SELECT id, image_url,
  VEC_DISTANCE(embedding, VEC_FROMTEXT('[...]')) AS dist
FROM image_gallery
ORDER BY dist
LIMIT 20;

-- 用户向量与物品向量的相似度匹配
SELECT item_id, item_name,
  VEC_DISTANCE_COSINE(item_embedding, VEC_FROMTEXT('[用户向量]')) AS similarity
FROM items
ORDER BY similarity
LIMIT 50;