Syntax Manual

Hint

This article is generated by AI translation.

jdbc-milvus parses SQL-style commands via ANTLR4 and converts them to Milvus SDK API calls. This document provides a detailed reference of all supported SQL syntax.

Version Requirements

Some advanced features (such as COUNT, BulkInsert) require Milvus 2.2+. Milvus 2.3.x or higher is recommended.

---

Database Management

Create Database

CREATE DATABASE [IF NOT EXISTS] db_name;

Drop Database

DROP DATABASE [IF EXISTS] db_name;

Alter Database Properties

ALTER DATABASE db_name SET PROPERTIES ("key" = "value", ...);

Show Databases

SHOW DATABASES;

---

Table Management (Collection)

Create Table

Supports defining primary keys, vector fields, and other attributes.

CREATE TABLE [IF NOT EXISTS] table_name (
    id       INT64 PRIMARY KEY,
    vector   FLOAT_VECTOR(128),
    age      INT32 DEFAULT 0
) WITH (
    consistency_level = "Strong"
);

Supported Field Types:

SQL Type	Milvus Type	Parameter
bool	Bool	—
int8 / int16 / int32 / int64	Int8 / Int16 / Int32 / Int64	—
float / double	Float / Double	—
json	JSON	—
varchar(N)	VarChar	N = max length
array	Array	—
float_vector(N)	FloatVector	N = dimension
binary_vector(N)	BinaryVector	N = dimension
float16_vector(N)	Float16Vector	N = dimension
bfloat16_vector(N)	BFloat16Vector	N = dimension
sparse_float_vector(N)	SparseFloatVector	N = dimension

Field Constraints:

Constraint	Description
PRIMARY KEY	Mark as primary key
AUTO_ID	Auto-generate ID
NOT NULL	Not-null constraint
DEFAULT value	Default value
COMMENT 'text'	Field comment

Full Create Table Example:

CREATE TABLE book_vectors (
    book_id    INT64 PRIMARY KEY,
    title      VARCHAR(256) NOT NULL COMMENT 'Book title',
    word_count INT32 DEFAULT 0,
    book_intro FLOAT_VECTOR(128)
) WITH (
    consistency_level = "Strong"
);

Drop Table

DROP TABLE [IF EXISTS] table_name;

Rename Table

ALTER TABLE old_name RENAME TO new_name;

Show Tables

-- List all tables
SHOW TABLES;

-- Show table info
SHOW TABLE table_name;

-- Show create table statement
SHOW CREATE TABLE table_name;

---

Index Management

Create Index

CREATE INDEX index_name ON TABLE table_name (vector_col)
    USING "IVF_FLAT" WITH (nlist = 1024, metric_type = "L2");

Supported Vector Index Types (USING):

• In-memory indexes: FLAT, IVF_FLAT, IVF_SQ8, IVF_PQ, HNSW, SCANN, AUTOINDEX
• Disk indexes: DISKANN
• GPU indexes: GPU_IVF_FLAT, GPU_IVF_PQ
• Sparse vector: SPARSE_INVERTED_INDEX

Supported Scalar Index Types:

• STL_SORT — Sort index, suitable for numeric fields
• Trie — Trie index, suitable for string fields
• INVERTED — Inverted index, suitable for keyword search

Mixed Vector and Scalar Index Example:

CREATE INDEX idx_intro ON TABLE book_vectors (book_intro)
    USING "IVF_FLAT" WITH (nlist = 1024, metric_type = "L2");

CREATE INDEX idx_title ON TABLE book_vectors (title) USING "INVERTED";

Drop Index

DROP INDEX index_name ON TABLE table_name;

Show Indexes

-- List all indexes of a table
SHOW INDEXES FROM table_name;

-- Show specific index info
SHOW INDEX index_name ON TABLE table_name;

-- Show index build progress
SHOW PROGRESS OF INDEX index_name ON TABLE table_name;

---

Partition Management

Create Partition

CREATE PARTITION [IF NOT EXISTS] partition_name ON TABLE table_name;

Drop Partition

DROP PARTITION [IF EXISTS] partition_name ON TABLE table_name;

Show Partitions

-- List all partitions of a table
SHOW PARTITIONS FROM table_name;

-- Show specific partition info
SHOW PARTITION partition_name ON TABLE table_name;

---

Alias Management

-- Create alias
CREATE ALIAS alias_name FOR TABLE table_name;

-- Alter alias target collection
ALTER ALIAS alias_name FOR TABLE table_name;

-- Drop alias
DROP ALIAS [IF EXISTS] alias_name;

---

Data Manipulation (DML)

Insert Data

-- Insert into default partition
INSERT INTO table_name (id, vector, age) VALUES (1, [0.1, 0.2], 10);

-- Insert into specific partition
INSERT INTO table_name PARTITION partition_name (id, vector) VALUES (2, [0.3, 0.4]);

Upsert (Insert or Overwrite)

-- Default partition
UPSERT INTO table_name (id, vector, age) VALUES (1, [0.1, 0.2], 10);

-- Specific partition
UPSERT INTO table_name PARTITION partition_name (id, vector) VALUES (2, [0.3, 0.4]);

Update Data

Note

Milvus Update is essentially "Search-to-Upsert": it first queries matching records, modifies the corresponding fields in memory, then Upserts them back. Full-table Update on large datasets is not recommended.

Basic Update (Scalar Filter)

-- Update by primary key
UPDATE table_name SET age = 20 WHERE id = 1;

-- Update by scalar condition (query then write back)
UPDATE table_name SET status = 'active' WHERE age > 18;

KNN Update

Update the K nearest records to the target vector. Underlying mechanism: execute TopK search → modify in memory → Upsert.

UPDATE table_name SET status = 1
    ORDER BY vector_col <-> [0.1, 0.2] LIMIT 1;

Range Update

Update all records within the specified distance (radius) of the target vector.

-- Using vector_range function (recommended)
UPDATE table_name SET tag = 'A'
    WHERE vector_range(vector_col, [0.1, 0.2], 0.5);

-- Using comparison expression
UPDATE table_name SET tag = 'A'
    WHERE vector_col <-> [0.1, 0.2] < 0.5;

Delete Data

Note

Milvus deletion is a logical delete, implemented internally by writing Delete logs. Vector-related deletions use a "Search-to-Delete" strategy (search for primary keys first, then delete).

Basic Delete (Scalar Filter)

-- Delete by primary key
DELETE FROM table_name WHERE id = 1;
DELETE FROM table_name WHERE id IN [1, 2, 3];

-- Delete by scalar condition
DELETE FROM table_name WHERE age > 18 AND status = 'inactive';

-- Delete from specific partition
DELETE FROM table_name PARTITION partition_name WHERE age > 10;

KNN Delete

Delete the K nearest records to the target vector. Underlying mechanism: execute TopK search to get primary keys → execute delete.

-- Delete the 100 nearest records to [0.1, 0.2]
DELETE FROM table_name
    ORDER BY vector_col <-> [0.1, 0.2] LIMIT 100;

-- Combined with scalar filter
DELETE FROM table_name
    WHERE category = 'book'
    ORDER BY vector_col <-> [0.1, 0.2] LIMIT 10;

Range Delete

Delete all records within the specified distance (radius) of the target vector. Underlying mechanism: execute Range Search to get primary keys → execute delete.

-- Using vector_range function (recommended)
DELETE FROM table_name
    WHERE vector_range(vector_col, [0.1, 0.2], 0.5);

-- Using comparison expression
DELETE FROM table_name
    WHERE vector_col <-> [0.1, 0.2] < 0.5;

-- Combined with LIMIT protection (delete at most 1000 records)
DELETE FROM table_name
    WHERE vector_range(vector_col, [0.1, 0.2], 0.5) LIMIT 1000;

Data Import

IMPORT FROM 'path/to/file.csv' INTO TABLE table_name;
IMPORT FROM 'file.json' INTO TABLE table_name PARTITION partition_name;

---

Load & Release

Milvus requires Collections to be loaded into memory before searching.

-- Load collection
LOAD TABLE table_name;

-- Load specific partition
LOAD TABLE table_name PARTITION partition_name;

-- Release collection
RELEASE TABLE table_name;

-- Release specific partition
RELEASE TABLE table_name PARTITION partition_name;

Show Loading Progress

SHOW PROGRESS OF LOADING ON TABLE table_name;
SHOW PROGRESS OF LOADING ON TABLE table_name PARTITION partition_name;

---

Query & Search (DQL)

This adapter uses unified SELECT syntax for both scalar queries (Query) and vector similarity searches (Search).

Scalar Query

-- Query all fields
SELECT * FROM table_name;

-- Conditional filter
SELECT * FROM table_name WHERE age > 20 AND status = 1;

-- Specify return fields with pagination
SELECT id, name FROM table_name LIMIT 10 OFFSET 0;

-- Query specific partition
SELECT * FROM table_name PARTITION partition_name WHERE tag = 'A';

Vector Search (KNN)

Use the <-> operator to express vector distance, combined with ORDER BY and LIMIT for TopK nearest neighbor search.

-- Basic search (default parameters)
SELECT * FROM table_name
    ORDER BY vector_col <-> [0.1, 0.2, ...] LIMIT 10;

-- With pre-filtering
SELECT * FROM table_name
    WHERE category = 'book'
    ORDER BY vector_col <-> [0.1, 0.2] LIMIT 5;

-- Using PreparedStatement parameter binding
SELECT * FROM table_name
    ORDER BY vector_col <-> ? LIMIT 10;

Vector Range Search

-- Method 1: Using vector_range function (recommended)
SELECT * FROM table_name
    WHERE vector_range(vector_col, [0.1, 0.2], 0.8) LIMIT 5;

-- Method 2: Using comparison expression
SELECT * FROM table_name
    WHERE vector_col <-> [0.1, 0.2] < 0.8 LIMIT 5;

Search Parameters (WITH Clause)

Use the WITH clause at the end of a SELECT statement to pass Milvus-specific search parameters.

SELECT id, score FROM table_name
    ORDER BY vector_col <-> [0.1, 0.2] LIMIT 10
    WITH (
        metric_type = "L2",
        params = "{\"nprobe\": 10}",
        consistency_level = "Strong"
    );

Supported Parameters:

• metric_type — Distance metric type (L2, IP, COSINE, etc.)
• params — Index-specific parameters (JSON string, e.g., {"nprobe": 10})
• consistency_level — Consistency level (Strong, Session, Bounded, Eventually)
• round_decimal — Result decimal precision

Count

tip

Requires Milvus server >= 2.2.0.

-- Count total rows
COUNT FROM table_name;

-- Count specific partition
COUNT FROM table_name PARTITION(partition_name);

-- Count with filter
COUNT FROM table_name WHERE age > 18;

Vector Data Formats

In statements containing vector operations (INSERT, SELECT with vector search, DELETE, etc.), multiple vector expression forms are supported:

1. JSON array literal — [0.1, 0.2, 0.3, ...]
2. JDBC parameter binding — ? (PreparedStatement), supports binding List<Float>, List<Double> (auto-converted), float[], double[]
3. Batch vectors — Pass multiple vectors at once in search: [[0.1, 0.2], [0.3, 0.4]]

Distance Operators

Operator	Meaning
<->	General distance (commonly used for L2)
<=>	Cosine distance
<#>	Inner product distance
~=	Approximate match
<%>	Percentage distance
<?>	Unknown/custom distance

---

User & Permission Management

User Management

CREATE USER [IF NOT EXISTS] username PASSWORD 'password';
DROP USER [IF EXISTS] username;
SHOW USERS;

Role Management

CREATE ROLE [IF NOT EXISTS] role_name;
DROP ROLE [IF EXISTS] role_name;
SHOW ROLES;

Grant & Revoke

-- Grant role to user
GRANT ROLE role_name TO username;
REVOKE ROLE role_name FROM username;

-- Grant privilege to role
GRANT privilege ON objectType objectName TO ROLE role_name;
REVOKE privilege ON objectType objectName FROM ROLE role_name;

-- Show role permissions
SHOW GRANTS FOR ROLE role_name ON GLOBAL;
SHOW GRANTS FOR ROLE role_name ON TABLE table_name;

---

Hint Support

Override or enhance query behavior through SQL Hints (/*+ ... */). Hints must be placed at the beginning of the SQL statement.

overwrite_find_limit

Force override the query's LIMIT (TopK).

/*+ overwrite_find_limit=5 */
SELECT * FROM table_name WHERE status = 1;

overwrite_find_skip

Force override the query's OFFSET.

/*+ overwrite_find_limit=5, overwrite_find_skip=10 */
SELECT * FROM table_name WHERE status = 1;

overwrite_find_as_count

Force convert the current query to a COUNT query (ignoring SELECT fields and sorting).

/*+ overwrite_find_as_count=true */
SELECT * FROM table_name WHERE age > 20;

consistency_level

Removed

The consistency_level Hint has been removed. Use the JDBC connection parameter consistencyLevel instead to set the consistency level at the connection level.

jdbc:dbvisitor:milvus://host:port?consistencyLevel=Strong

See Connection Parameters for details.

---

Data Flush

Flush the in-memory data of a specified collection to persistent storage.

FLUSH collection_name;

tip

In most scenarios, using the consistencyLevel=Strong connection parameter is recommended over FLUSH, as FLUSH is a heavy persistence operation. FLUSH is more suitable for ensuring data is persisted after bulk writes.

---

Progress Monitoring

-- Show loading progress
SHOW PROGRESS OF LOADING ON TABLE table_name;
SHOW PROGRESS OF LOADING ON TABLE table_name PARTITION partition_name;

-- Show index build progress
SHOW PROGRESS OF INDEX ON TABLE table_name;
SHOW PROGRESS OF INDEX index_name ON TABLE table_name;