Syntax and examples of AI functions|V4.6.0|OceanBase Database| docs|Distributed Database

Syntax and examples of AI functions

Last Updated：2026-08-25 01:59:18 Updated

This topic describes the syntax and parameters of AI functions in OceanBase Database and provides examples of how to use them. Currently, the following AI functions are supported: AI_SPLIT_DOCUMENT, AI_EMBED, AI_COMPLETE, AI_PROMPT, and AI_RERANK.

AI functions integrate AI model capabilities directly into data processing within a database using SQL expressions. This greatly simplifies operations such as data reading, analysis, summarization, and saving using large AI models, making it an important new feature in the field of databases and data warehouses. In MySQL-compatible mode, OceanBase Database provides AI model and endpoint management through the DBMS_AI_SERVICE package. It also includes several built-in AI function expressions and supports monitoring AI model calls via views.

Prerequisites

Notice

This section does not apply when using the AI_SPLIT_DOCUMENT function.

You have the required permissions for AI models. For details, see Permissions for AI function services.
If you have not registered an AI model, register one first by following the instructions in Register AI models.

Considerations

Notice

You do not need to pay attention to this section when using the AI_SPLIT_DOCUMENT function.

Notice

Considerations only apply to the old API. You do not need to pay attention to them when using the new API.

The CREATE AI MODEL and DROP AI MODEL operations are synchronized between primary and standby tenants, but the CREATE AI MODEL ENDPOINT, ALTER AI MODEL ENDPOINT, and DROP AI MODEL ENDPOINT operations are not. Therefore, you must manually configure an AI model endpoint for a standby tenant to use AI function services.
Hybrid Search relies on the model management and embedding features of AI function services. When deleting an AI model, check whether it is referenced by Hybrid Search to avoid potential issues.

Syntax and examples of functions

This section describes the syntax and examples of AI function services.

AI_SPLIT_DOCUMENT

The AI_SPLIT_DOCUMENT function splits text into multiple chunks to prepare it for further processing.

Syntax

The syntax is as follows:

AI_SPLIT_DOCUMENT(content TEXT, [parameters JSON]) [AS alias_name]

Parameter description:

Parameter	Description	Type	Nullable
content	Text data entered by users currently only supports markdown format and UTF-8 encoding.	VARCHAR / TEXT	No. This parameter is required. If it is empty, an empty table is returned.
parameters	Optional parameters supported by the configuration API. The value must be a JSON object or a string in JSON format. Currently, the following parameters are supported: `type`: The value can be `text` or `markdown`. The default value is `markdown`. `by`: The value can be `word` or `sentence`, indicating whether to split the text by word count or sentence count. The default value is `word`. `max`: The maximum number of words (or sentences) that can be split in each chunk. The default value is 256. Value range: [1,1000]. `overlap`: The maximum number of overlapping words (or sentences) between adjacent chunks. The default value is 0 and cannot exceed half of the maximum value.	JSON	Yes
alias_name	Specifies an alias for the returned result.	VARCHAR(128)	Yes

Return value:

Returns a relational table containing four fields: CHUNK_ID, CHUNK_OFFSET, CHUNK_LENGTH, and CHUNK_TEXT. Here, CHUNK_ID is the ID of the split chunk, CHUNK_OFFSET is the starting position of the split chunk, CHUNK_LENGTH is the length of the split chunk in bytes, and CHUNK_TEXT is the text content of the split chunk.

Limitations:

Currently, only UTF-8 encoded data can be split.
This function can only be used as a table function after the FROM clause. It cannot be directly used as a scalar function in the SELECT list.

Example

SELECT * FROM ai_split_document("Hello World",'{"max":1}');

The return result is as follows:

+----------+--------------+--------------+------------+
| CHUNK_ID | CHUNK_OFFSET | CHUNK_LENGTH | CHUNK_TEXT |
+----------+--------------+--------------+------------+
| 0 | 0 | 6 | Hello |
| 1 | 6 | 6 | World |
+----------+--------------+--------------+------------+

AI_EMBED

The AI_EMBED function uses model_key to specify a registered embedding model and converts user-provided text or image data into vector data. If the model supports multiple dimensions, you can use the dim parameter to specify the output vector dimension. For image input, use the JSON parameter {"type":"image"} to specify the input type.

Syntax

The syntax is as follows:

AI_EMBED(model_key, input, [dim_or_parameters])

Parameter description:

Parameter	Description	Type	Nullable
model_key	The model identifier. For the legacy API, specify the registered `model_key`. For the new API, supported starting with V4.6.0 BP1, use the `provider/model` format, such as `aliyun/qwen-plus`.	VARCHAR(128)	No
input	The text or image data to convert. Text input is a string. Image input can be an HTTPS URL or binary data passed by using `FROM_BASE64(...)`.	VARCHAR	No
dim_or_parameters	The optional third parameter. For text embedding, you can specify `dim` (INT64) to set the output vector dimension. For image embedding, pass the JSON string `'{"type":"image"}'`. Notice Image embedding is supported starting with V4.6.0 Hotfix1.	INT64 / JSON	Yes

If model or input is specified and the other is NULL, the function returns an error.

Return value:

A string in vector format, which is the vector converted from the text by the embedding model.

Examples

New API

Old API

Embed single-row data

SELECT AI_EMBED("aliyun/qwen-plus","Hello world") AS embedding;

The return result is as follows:

+----------------+
| embedding      |
+----------------+
| [0.1, 0.2, 0.3]|
+----------------+

Embed columns from a table

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT
);

INSERT INTO comments (content) VALUES ('hello world!');

SELECT AI_EMBED("aliyun/qwen-plus",content) AS embedding FROM comments;

The return result is as follows:

+----------------+
| embedding      |
+----------------+
| [0.1, 0.2, 0.3]|
+----------------+

Embed single-row data

SELECT AI_EMBED("ob_embed","Hello world") AS embedding;

The return result is as follows:

+----------------+
| embedding      |
+----------------+
| [0.1, 0.2, 0.3]|
+----------------+

Embed columns from a table

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT
);

INSERT INTO comments (content) VALUES ('hello world!');

SELECT AI_EMBED("ob_embed",content) AS embedding FROM comments;

The return result is as follows:

+----------------+
| embedding      |
+----------------+
| [0.1, 0.2, 0.3]|
+----------------+

Embed images

SET @img_url = 'https://example.com/image.jpg';

-- Vectorize an image URL
SELECT AI_EMBED('aliyun-dashscope/qwen2.5-vl-embedding', @img_url, '{"type":"image"}') AS embedding;

-- Vectorize binary image data (Base64)
SELECT AI_EMBED('aliyun-dashscope/qwen2.5-vl-embedding', FROM_BASE64(@img_base64), '{"type":"image"}') AS embedding;

Perform image-to-image search

Use cosine_distance to compare image-vector similarity:

SET @query_vec = AI_EMBED('aliyun-dashscope/qwen2.5-vl-embedding', @img_url, '{"type":"image"}');
SELECT
    id,
    cosine_distance(
        AI_EMBED('aliyun-dashscope/qwen2.5-vl-embedding', image_url, '{"type":"image"}'),
        @query_vec
    ) AS distance
FROM image_table
WHERE image_url IS NOT NULL
ORDER BY distance ASC
LIMIT 3;

AI_COMPLETE and AI_PROMPT

The AI_COMPLETE function uses model_key to specify a registered text-generation large language model (LLM), processes the user-provided prompt and data, and returns text generated by the LLM. You can use the prompt parameter to customize how prompts and in-database data are organized. Together with image placeholders in AI_PROMPT, this function can also process and analyze images. This approach supports diverse text processing and batch processing in the database, avoiding repeated data transfer between the database and the LLM.

Considering that prompts in many AI application scenarios are often highly structured and require dynamic injection of specific data, manually concatenating prompts with input content using functions like CONCAT is not only costly but also prone to format errors. To support the reuse of prompts and dynamic combination of prompts with data, OceanBase Database provides the AI_PROMPT function. AI_PROMPT upgrades prompts from "static text" to a "reusable, parametric" functional template form, which can be used directly in AI_COMPLETE to replace the prompt parameter, greatly simplifying the prompt construction process and improving development efficiency and accuracy.

AI_PROMPT function

The AI_PROMPT function is used to dynamically construct formatted prompts based on a prompt template, supporting dynamic data insertion.

Syntax

The syntax for the AI_PROMPT function is as follows:

AI_PROMPT('template', expr0 [ , expr1, ... ]);

Parameter description:

Parameter	Description	Type	Nullable
template	The user-provided prompt template. It supports text placeholders such as `{0}` and `{1}`, and image placeholders such as `{img_0}` and `{img_1}`.	VARCHAR(max_length)	No
expr	The user-provided data. A text placeholder corresponds to a string. An image placeholder corresponds to an HTTPS URL or binary data returned by `FROM_BASE64(...)`. Notice Image processing is supported starting with V4.6.0 Hotfix1.	VARCHAR(max_length)	No

The template and expr parameters are required and cannot be empty. The expr parameter only supports the VARCHAR type, not the JSON type.

Notice

The index of parameter placeholders in a prompt template must be unique; otherwise, an error will occur. For example, AI_PROMPT("tell me {0}+{0}=?", '10', '10') contains multiple {0} in the template, which is not allowed. The correct usage is to have each placeholder appear only once, for example, AI_PROMPT("tell me {0}+{1}=?", '10', '10').

Return value:

The return value is the formatted prompt in JSON format. For an image specified by URL, the JSON object contains the type=image and url fields. For a binary image, the JSON object contains the type=image, format, and data fields.

Example

The AI_PROMPT function organizes the user-provided template string and dynamic data into JSON so that downstream AI functions can automatically replace and reuse them. The two main scenarios are text placeholders and image placeholders, which let you insert text and image parameters into a prompt template.

Use text placeholders

Use {0}, {1}, and similar placeholders in the template. They are automatically replaced with text based on the order of the corresponding parameters. This is useful for dynamically generating prompts that contain specific data, such as quantities or item names.

Example:

SELECT AI_PROMPT('Recommend {0} of the most popular {1} to me.', 'ten', 'mobile phones');

The result is as follows:

{
"template": "Recommend {0} of the most popular {1} to me.",
"args": ["ten", "mobile phones"]
}

Use AI_PROMPT in AI_COMPLETE:

New API

Legacy API

SELECT AI_COMPLETE(
    "aliyun/qwen-plus",AI_PROMPT('Recommend {0} of the most popular {1} to me.just output name in json array format', 'two', 'mobile phones')
    ) AS ans;

The return result is as follows:

+--------------------------------------------------+
| ans                                              |
+--------------------------------------------------+
| ["iPhone 15 Pro Max","Samsung Galaxy S24 Ultra"] |
+--------------------------------------------------+

SELECT AI_COMPLETE(
    "ob_complete",
    AI_PROMPT('Recommend {0} of the most popular {1} to me. just output name in json array format', 'two', 'mobile phones')
    ) AS ans;

The return result is as follows:

+--------------------------------------------------+
| ans                                              |
+--------------------------------------------------+
| ["iPhone 15 Pro Max","Samsung Galaxy S24 Ultra"] |
+--------------------------------------------------+

Use image placeholders

Use {img_0}, {img_1}, and similar placeholders in the template, and pass image URLs or binary data as the corresponding parameters. This enables multimodal large language models to process image content.

The following rules apply to image placeholders:
- If all parameters are images, use {img_0}, {img_1}, and so on in order. Each image parameter has an index starting from 0, independently of text placeholders such as {0} and {1}.
- If image and text parameters are mixed, N in {img_N} is the position of that parameter among all parameters, with the index starting from 0. It uses the same index as the text placeholder {N}. For example, if the second parameter at index 1 is an image, use {img_1}. If the fourth parameter at index 3 is an image, use {img_3}. Do not number images separately.
Examples:
```
-- Single image URL
SELECT AI_PROMPT('Describe this image {img_0}', @img_url) AS result;

-- Compare multiple images
SELECT AI_PROMPT(
    'Compare the differences between these two images: {img_0} and {img_1}',
    @img_url_1,
    @img_url_2
) AS result;

-- Mix text and an image (the image is at index 1, so use {img_1})
SELECT AI_PROMPT(
    'Describe image {img_1} in {0} sentences',
    '3',
    @img_url
) AS result;

-- Interleave text and images (the second image is at index 3, so use {img_3})
SELECT AI_PROMPT(
    'Compare the {0} image {img_1} with the {2} image {img_3}, and summarize the differences in one sentence',
    'JPEG',
    @img_jpeg,
    'PNG',
    @img_png
) AS result;
```

This format lets you flexibly construct structured, multimodal AI prompts for automated, intelligent batch processing.

AI_COMPLETE function

Syntax

The syntax for the AI_COMPLETE function is as follows:

AI_COMPLETE(model, prompt[, parameters])
--If using the AI_PROMPT function, replace the `prompt` parameter with the AI_PROMPT function. For examples, see the AI_PROMPT function.
AI_COMPLETE(model, AI_PROMPT(prompt_template, data))

Parameter description:

Parameter	Description	Type	Nullable
model_key	The model identifier. For the legacy API, specify the registered `model_key`. For the new API, supported starting with V4.6.0 BP1, use the `provider/model` format, such as `aliyun/qwen-plus`.	VARCHAR(128)	No
prompt	The prompt information entered by the user.	VARCHAR/TEXT(LONGTEXT)	No
parameters	Used to configure optional parameters provided by the API. The optional fields of a model are directly included in the generated message body, which may vary among different providers. Common optional parameters include `temperature`, `top_p`, and `max_tokens`. In most cases, you do not need to specify anything; the default configuration is sufficient.	JSON	Yes

If either model or prompt is specified as NULL, the function returns an error.

Return value:

text, the text generated by the large language model based on the prompt.

Examples

New API

Old API

Sentiment analysis example

SELECT AI_COMPLETE("aliyun/qwen-plus","Your task is to perform sentiment analysis on the provided text and determine whether its sentiment tendency is positive or negative.
The following is the text to be analyzed:
<text>
What nice weather!
</text>
The judgment criteria are as follows:
If the text expresses a positive sentiment, output 1; if the text expresses a negative sentiment, output -1. Do not output anything else.\n") AS ans;

The return result is as follows:

+-----+
| ans |
+-----+
| 1   |
+-----+

Translation example

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT
);

INSERT INTO comments (content) VALUES ('hello world!');

-- By replacing the processed data with column names in a table using concatenation expressions, you can naturally perform batch processing on data in the database without copying data from the database to a large model and then back.
SELECT AI_COMPLETE("aliyun/qwen-plus",
concat("You are a translation master and need to translate the following English text into German. The text to be translated is: <text>",
    content,
    "</text>")) AS ans FROM comments;

The return result is as follows:

+-------------+
| ans         |
+-------------+
| Hallo Welt! |
+-------------+

Classification example

SELECT AI_COMPLETE("aliyun/qwen-plus","You are a classification master. You will receive a bunch of question texts, and you need to distinguish their categories. The category list is [\"Hardware Department\",\"Software Department\",\"Other\"]. The following is the text to be analyzed:
<text>
The quality of this screen is really poor.
</text>") AS res;

The return result is as follows:

+--------+
| res    |
+--------+
| Hardware Department |
+--------+

Sentiment analysis example

SELECT AI_COMPLETE("ob_complete","Your task is to perform sentiment analysis on the provided text and determine whether its sentiment tendency is positive or negative.
The following is the text to be analyzed:
<text>
What nice weather!
</text>
The judgment criteria are as follows:
If the text expresses a positive sentiment, output 1; if the text expresses a negative sentiment, output -1. Do not output anything else.\n") AS ans;

The return result is as follows:

+-----+
| ans |
+-----+
| 1   |
+-----+

Translation example

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT
);

INSERT INTO comments (content) VALUES ('hello world!');

-- By replacing the processed data with column names in a table using concatenation expressions, you can naturally perform batch processing on data in the database without copying data from the database to a large model and then back.
SELECT AI_COMPLETE("ob_complete",
concat("You are a translation master and need to translate the following English text into German. The text to be translated is: <text>",
    content,
    "</text>")) AS ans FROM comments;

The return result is as follows:

+-------------+
| ans         |
+-------------+
| Hallo Welt! |
+-------------+

Classification example

SELECT AI_COMPLETE("ob_complete","You are a classification master. You will receive a list of question texts and need to categorize them. The possible categories are ['Hardware', 'Software', 'Other']. The following is the text to be analyzed:
<text>
This screen has really poor quality.
</text>") AS res;

The return result is as follows:

+--------+
| res    |
+--------+
| Hardware |
+--------+

Process images

Notice

This feature is supported starting with V4.6.0 Hotfix1.

SET @img_url = 'https://example.com/image.jpg';

-- Process a single image (URL)
SELECT AI_COMPLETE(
    'aliyun-dashscope/qwen3.5-plus',
    AI_PROMPT('Describe this image {img_0}', @img_url)
) AS result;

-- Process a single image (Base64)
SELECT AI_COMPLETE(
    'aliyun-dashscope/qwen3.5-plus',
    AI_PROMPT('Describe this image {img_0}', FROM_BASE64(@img_base64))
) AS result;

-- Compare multiple images
SELECT AI_COMPLETE(
    'aliyun-dashscope/qwen3.5-plus',
    AI_PROMPT(
        'Compare the {0} image {img_1} with the {2} image {img_3}, and summarize the differences in one sentence',
        'JPEG',
        @img_jpeg,
        'PNG',
        @img_png
    )
) AS result;

AI_RERANK

The AI_RERANK function specifies a registered reranking model through the model parameter. It organizes the user-provided query terms and document list according to provider rules, sends the message to the specified model, and parses and returns the ranking result. This is suitable for reranking scenarios in RAG.

Usage

The syntax is as follows:

AI_RERANK(model, query, documents[, document_key])

Parameter description:

Parameter	Description	Type	Nullable
model	The name of the model registered in the database. Valid values are a name in the `provider/model` format, such as `aliyun/gte-rerank-v2`, or the name of a custom registered model, such as `ob_rerank`.	VARCHAR(128)	No
query	The text entered by the user.	VARCHAR(1024)	No
documents	The list of documents entered by the user.	JSON ARRAY, for example, `'["apple", "banana"]'`	No

When the function is called, you must specify model, query, and documents. If any one of them is NULL, an error is returned.

Return value:

A JSON array containing the documents returned by the reranking model along with their relevance scores, sorted in descending order by relevance score.

Examples

New API

Old API

SELECT AI_RERANK("aliyun/gte-rerank-v2","Apple",'["apple","banana","fruit","vegetable"]');

The return result is as follows:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ai_rerank("aliyun/gte-rerank-v2","Apple",'["apple","banana","fruit","vegetable"]')                                                                                                                                                                                                                                                                        |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"index": 0, "document": {"text": "apple"}, "relevance_score": 0.9912109375}, {"index": 1, "document": {"text": "banana"}, "relevance_score": 0.0033512115478515625}, {"index": 2, "document": {"text": "fruit"}, "relevance_score": 0.0003669261932373047}, {"index": 3, "document": {"text": "vegetable"}, "relevance_score": 0.00001996755599975586}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT AI_RERANK("ob_rerank","Apple",'["apple","banana","fruit","vegetable"]');

The return result is as follows:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ai_rerank("ob_rerank","Apple",'["apple","banana","fruit","vegetable"]')                                                                                                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"index": 0, "document": {"text": "apple"}, "relevance_score": 0.9912109375}, {"index": 1, "document": {"text": "banana"}, "relevance_score": 0.0033512115478515625}, {"index": 2, "document": {"text": "fruit"}, "relevance_score": 0.0003669261932373047}, {"index": 3, "document": {"text": "vegetable"}, "relevance_score": 0.00001996755599975586}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

References

Register AI models: The complete commands for registering models and their endpoints.
Quick start with AI Function Service: A guide for first-time users, covering the minimum steps from registration to running the first example.
Vector embedding technology
Privilege types in MySQL-compatible mode

OceanBase

Customer Stories

Documentation

Syntax and examples of AI functions

Prerequisites

Notice

Considerations

Notice

Notice

Syntax and examples of functions

AI_SPLIT_DOCUMENT

Syntax

Example

AI_EMBED

Syntax

Notice

Examples

AI_COMPLETE and AI_PROMPT

AI_PROMPT function

Syntax

Notice

Notice

Example

AI_COMPLETE function

Syntax

Examples

Notice

AI_RERANK

Usage

Examples

References