AI Function

AI_PROMPT

Invokes an LLM with a prompt to perform inference on text and output the result. Supports advanced capabilities such as setting a role through the system prompt, controlling generation diversity with the temperature, and chain-of-thought reasoning (Qwen3 series).

Syntax

-- Basic call. The default LLM model is used automatically
SELECT AI_PROMPT(prompt)

-- Specify a model
SELECT AI_PROMPT(prompt, model)

-- Specify a model and generation parameters
SELECT AI_PROMPT(prompt, model, options)

Parameters

• prompt: Required. The input prompt. Character types (CHAR, VARCHAR, and TEXT) are supported.

• model: Optional. The name of the model used by the AI Function. If omitted, the default LLM model configured in the AI gateway is used. If explicitly specified, the model name must come from the results of SHOW AI MODEL.

• options: Optional. A JSON string that controls the generation behavior of the LLM. The fields are described as follows:

  Field	Type	Description
  temperature	DOUBLE	The temperature parameter (0 to 2). A higher value produces more random results.
  max_tokens	INT	The maximum number of tokens to generate.
  top_p	DOUBLE	The nucleus sampling parameter.
  stop	STRING / ARRAY	The stop sequences for generation.
  system_prompt	STRING	The system prompt for setting the AI role.
  enable_thinking	BOOLEAN	Enables the chain-of-thought reasoning mode (Qwen3 series). Default value: FALSE.

Return value

• Returns the answer of the LLM to the question. The type is TEXT.

• Enterprise Edition: an error is returned if the prompt parameter is NULL or an empty string (""). Standard Edition: NULL is returned if prompt is NULL, and an error is returned if it is an empty string ("").

Examples

• Basic inference

  SELECT AI_PROMPT('What is PolarDB-X? Answer in one sentence.');

  The following result is returned.

  AI_PROMPT
  ---------
  PolarDB-X is a cloud-native distributed database independently developed by Alibaba. It is compatible with the MySQL protocol and supports high-concurrency real-time processing and analysis of massive amounts of data.

• Specify a model. First use SHOW AI MODEL to obtain the model names actually available on the current instance.

  SHOW AI MODEL;
  
  -- Replace <model_name> with an available LLM model name returned by SHOW AI MODEL
  SELECT AI_PROMPT('Explain what a distributed database is in one sentence', '<model_name>');

• Set an AI role

  SELECT AI_PROMPT(
      'Please help me optimize this SQL: SELECT * FROM orders WHERE status = 1',
      '', -- An empty string means the default model is used
      '{"system_prompt": "You are a senior database DBA specializing in SQL performance optimization."}'
  );

• Enable chain-of-thought reasoning

  SELECT AI_PROMPT(
      'Analyze the time complexity of this code and provide optimization suggestions: for(int i=0;i<n;i++) for(int j=i;j<n;j++) sum+=a[j];',
      '', -- An empty string means the default model is used
      '{"enable_thinking": true}'
  );

• Generate a description for each row based on table data

  SELECT
      name,
      AI_PROMPT(CONCAT('Describe this city in one sentence: ', name)) AS description
  FROM city
  LIMIT 3;

AI_EMBEDDING

Converts the input text into a fixed-dimension continuous vector (a JSON array) for scenarios such as semantic search, clustering, and recommendation.

Syntax

-- Basic call. The default EMBEDDING model is used automatically
SELECT AI_EMBEDDING(text)

-- Specify a model
SELECT AI_EMBEDDING(text, model)

-- Specify a model and the vector dimension
SELECT AI_EMBEDDING(text, model, options)

Parameters

• text: Required. The input text. Character types (CHAR, VARCHAR, and TEXT) are supported.

• model: Optional. The name of the model used by the AI Function. If omitted, the default EMBEDDING model configured in the AI gateway is used. If explicitly specified, the model name must come from the results of SHOW AI MODEL.

• options: Optional. A JSON string. You can specify the vector dimension.

  Field	Type	Description
  dimension	INT	The vector dimension. By default, it is determined by the model.

Return value

• Returns a string in JSON array format. The content is an array of floating-point numbers, and the length is determined by the model or the dimension parameter.

• Enterprise Edition: an error is returned if the text parameter is NULL or an empty string (""). Standard Edition: NULL is returned if text is NULL or an empty string ("").

Examples

• Basic embedding

  SELECT AI_EMBEDDING('PolarDB-X is a distributed database');

  The return value is a JSON array, for example:

  AI_EMBEDDING
  ------------
  [0.123, -0.456, 0.789, ...]

• Specify the vector dimension

  SELECT AI_EMBEDDING(
      'PolarDB-X is a distributed database',
      '', -- An empty string means the default model is used
      '{"dimension": 512}'
  );

• Persist vectors (compute once, reuse forever)

  -- The following example uses 1024 dimensions. The actual dimension must match the output dimension of AI_EMBEDDING
  CREATE TABLE documents (
      id BIGINT PRIMARY KEY AUTO_INCREMENT,
      content TEXT,
      embedding VECTOR(1024),
      VECTOR INDEX idx_embedding(embedding) DISTANCE=COSINE
  ) PARTITION BY KEY(id) PARTITIONS 4;
  
  -- Generate and store vectors in batches
  UPDATE documents
  SET embedding = VEC_FROMTEXT(AI_EMBEDDING(content))
  WHERE embedding IS NULL;

  Note

  Before you use vector columns and vector indexes, you must enable the vector index feature: set the system variable vidx_disabled to OFF. This variable is an inverse switch, and the default value ON means the feature is disabled. For more information, see the native vector index (Vector) documentation.

AI_SIMILARITY

Computes the cosine similarity, Euclidean distance, or dot product between two texts, two vectors, or a text and a vector. Only when both inputs are vectors is the computation performed locally without calling the Embedding API. If either input is text, the text must first be converted into a vector through the Embedding API.

Syntax

SELECT AI_SIMILARITY(query, text_or_vector [, similarity_type [, model]])

Parameters

Return value

• cosine: Returns the cosine similarity in the range of [-1, 1]. A larger value indicates higher similarity. Sort results in descending order for retrieval.

• euclidean: Returns the Euclidean distance in the range of [0, +infinity). A smaller value indicates higher similarity. Sort results in ascending order for retrieval.

• dot: Returns the dot product of the vectors. The value can be any real number. Generally, a larger value indicates higher correlation, but the result is affected by the vector norms.

• Enterprise Edition: the statement raises an error if the input is empty, the vector format is invalid, or the dimensions of the two vectors are inconsistent. Standard Edition: NULL is returned if the input is NULL.

Examples

• Text-to-text similarity

  SELECT AI_SIMILARITY('cloud-native database', 'PolarDB-X is a distributed cloud-native database') AS score;

  The following result is returned.

  score
  -----
  0.87

• Compute similarity with a pre-stored vector

  -- Generate the query vector only once to avoid repeated calls to the Embedding API during row-by-row computation
  SET @query_embedding = AI_EMBEDDING('distributed transaction expert');
  
  SELECT id, name,
      AI_SIMILARITY(@query_embedding, VEC_TOTEXT(embedding), 'cosine') AS score
  FROM resumes
  WHERE status = 'processed' AND embedding IS NOT NULL
  ORDER BY score DESC
  LIMIT 10;

AI_RANK

Uses a cross-encoder reranking model to score the deep semantic matching between a query and candidate documents. It is more accurate than vector cosine similarity and is commonly used as the secondary reranking stage of recall results.

Syntax

SELECT AI_RANK(query, candidate [, model [, options]])

Parameters

• query: Required. The query text. Character types (CHAR, VARCHAR, and TEXT) are supported.

• candidate: Required. The candidate text to be compared with the query for relevance. Character types (CHAR, VARCHAR, and TEXT) are supported.

• model: Optional. The name of the model used by the AI Function. If omitted, the default RERANK model configured in the AI gateway is used. If explicitly specified, the model name must come from the results of SHOW AI MODEL.

• options: Optional. A JSON string that controls the reranking parameters.

Return value

• Returns a relevance score of the DOUBLE type in the range of [0, 1]. A larger value indicates higher relevance.

• Enterprise Edition: an error is returned if either the query or candidate parameter is NULL. Standard Edition: NULL is returned if either parameter is NULL.

Examples

• Basic scoring

  SELECT AI_RANK(
      'How to optimize database query performance',
      '10 best practices for database index design and query optimization'
  ) AS score;

  The following result is returned.

  score
  -----
  0.91

• Rerank a list of documents

  SELECT id, title,
      AI_RANK('PolarDB-X distributed transaction', content) AS relevance
  FROM articles
  WHERE category = 'database'
  ORDER BY relevance DESC
  LIMIT 10;

• Recommended usage: two-stage retrieval (vector recall first, then reranking)

  SELECT id, name, summary, similarity_score,
      AI_RANK('Senior Java backend engineer', summary) AS rank_score
  FROM (
      SELECT id, name, summary,
          AI_SIMILARITY('Senior Java backend engineer', embedding) AS similarity_score
      FROM resumes
      WHERE embedding IS NOT NULL AND summary IS NOT NULL
      ORDER BY similarity_score DESC
      LIMIT 20
  ) recalled
  ORDER BY rank_score DESC;

AI_CLASSIFY

Classifies text into the candidate labels you specify in a zero-shot manner. No training is required. You only need to provide the candidate label list. Supports both single-label (default) and multi-label modes.

Syntax

SELECT AI_CLASSIFY(text, labels_json [, model [, options]])

Parameters

• text: Required. The text to classify. Character types (CHAR, VARCHAR, and TEXT) are supported.

• labels_json: Required. The list of classification labels in JSON array format. The ARRAY type is supported. We recommend 2 to 20 labels.

• model: Optional. The name of the model used by the AI Function. If omitted, the default LLM model configured in the AI gateway is used.

• options: Optional. A JSON string.

  Field	Type	Description
  multi_label	BOOLEAN	Enables multi-label classification mode and returns a JSON array of matched labels. Default value: FALSE (single-label).

Return value

• In single-label mode, returns the matched label string.

• In multi-label mode, returns multiple labels as a JSON array.

• Enterprise Edition: an error is returned if the text parameter is NULL or an empty string (""). Standard Edition: NULL is returned if text is NULL, and an error is returned if it is an empty string ("").

Examples

• Single-label classification (sentiment analysis)

  SELECT AI_CLASSIFY(
      'I have used this product for a week. The quality is great and I highly recommend it!',
      '["positive", "negative", "neutral"]'
  ) AS sentiment;

  The following result is returned.

  sentiment
  ---------
  positive

• Multi-label classification

  SELECT AI_CLASSIFY(
      'This article introduces best practices for deploying a distributed database using Kubernetes',
      '["database", "cloud native", "operations", "development"]',
      '',
      '{"multi_label": true}'
  ) AS tags;

  The following result is returned.

  tags
  ----
  ["database", "cloud native", "operations"]

• Batch classification write-back

  UPDATE resumes
  SET category = AI_CLASSIFY(
      raw_text,
      '["frontend", "backend", "algorithm", "data", "operations", "other"]'
  )
  WHERE category IS NULL AND status = 'processed';

AI_EXTRACT

Extracts structured fields from unstructured text based on field description objects and returns a string in JSON object format. You can use functions such as JSON_EXTRACT to expand the result into columns. The field description objects here are not standard JSON Schema.

Syntax

SELECT AI_EXTRACT(text, fields_json [, model [, options]])

Parameters

• text: Required. The input text. Character types (CHAR, VARCHAR, and TEXT) are supported.

• fields_json: Required. A string in JSON object format. The keys are the field names to extract, and the values describe the meaning of each field, for example {"name":"full name","skills":"skill list"}.

• model: Optional. The name of the model used by the AI Function. If omitted, the default LLM model configured in the AI gateway is used.

• options: Optional. A JSON string. Supports temperature, max_tokens, and system_prompt.

Return value

• Returns a string in JSON object format that contains the extraction result of each field. The result can be parsed by JSON functions.

• Enterprise Edition: an error is returned if the text parameter is NULL or an empty string (""). Standard Edition: NULL is returned if text is NULL, and an error is returned if it is an empty string ("").

Examples

• Extract resume information

  SELECT AI_EXTRACT(
      'Zhang San, 8 years of Java development experience, graduated from the Department of Computer Science at Shanghai Jiao Tong University, previously worked at Alibaba, familiar with Spring Boot, Kafka, and MySQL.',
      '{"name": "full name", "experience_years": "years of experience", "university": "university", "skills": "skill list", "company": "previous employer"}'
  ) AS info;

  The following result is returned.

  {
    "name": "Zhang San",
    "experience_years": "8 years",
    "university": "Shanghai Jiao Tong University",
    "skills": "Java, Spring Boot, Kafka, MySQL",
    "company": "Alibaba"
  }

• Expand the result into columns after extraction

  SELECT
      id,
      JSON_UNQUOTE(JSON_EXTRACT(info, '$.name'))       AS name,
      JSON_UNQUOTE(JSON_EXTRACT(info, '$.university')) AS university,
      JSON_UNQUOTE(JSON_EXTRACT(info, '$.skills'))     AS skills
  FROM (
      SELECT id,
          AI_EXTRACT(raw_text,
              '{"name":"full name","university":"school","skills":"skills"}') AS info
      FROM resumes
  ) t;

AI_SUMMARIZE

Compresses long text into a summary within the specified maximum number of characters while retaining the core information. Supports specifying the output language and the summary style.

Syntax

SELECT AI_SUMMARIZE(text [, max_length [, model [, options]]])

Parameters

• text: Required. The input text. Character types (CHAR, VARCHAR, and TEXT) are supported.

• max_length: Optional. The maximum number of characters of the summary. Default value: 200. If the value is 0 or negative, the default value is used. This does not mean that the length limit is lifted.

• model: Optional. The name of the model used by the AI Function. If omitted, the default LLM model configured in the AI gateway is used.

• options: Optional. A JSON string.

  Field	Type	Description
  language	STRING	Specifies the output language of the summary, such as "Chinese" or "English".
  style	STRING	The summary style. Valid values: (Default) Coherent paragraph bullet_points: bullet list

Return value

• Returns the summary content of the TEXT type.

• Enterprise Edition: an error is returned if the text parameter is NULL or an empty string (""). Standard Edition: NULL is returned if text is NULL, and an error is returned if it is an empty string ("").

• If the value of max_length is 0 or negative, the default value is used.

Examples

• Basic summarization

  SELECT AI_SUMMARIZE(raw_text, 200) AS summary
  FROM resumes
  WHERE id = 1;

• Specify the output language (output a Chinese summary even if the source text is in English)

  SELECT AI_SUMMARIZE(raw_text, 200, '', '{"language": "Chinese"}') AS summary
  FROM resumes
  WHERE id = 1;

• Bullet-point style

  SELECT AI_SUMMARIZE(raw_text, 300, '', '{"style": "bullet_points"}') AS summary
  FROM resumes
  WHERE id = 1;

• Generate summaries in batches and store them

  UPDATE resumes
  SET summary = AI_SUMMARIZE(raw_text, 200)
  WHERE summary IS NULL AND status = 'processed';

AI_PARSE_DOCUMENT

Parses unstructured files such as public PDFs, Word, PPT, TXT, Markdown, HTML, and images, and converts their content into plain text. Supports automatic file type recognition.

Syntax

SELECT AI_PARSE_DOCUMENT(url [, input_format [, model [, options]]])

Parameters

Return value

• Returns the TEXT type, which is the plain text content parsed from the file.

• If the URL is invalid, the file cannot be accessed, the file content is not supported, or parsing fails, the statement raises an SQL exception. The caller must catch and handle the exception.

Examples

• Parse a public PDF

  SELECT AI_PARSE_DOCUMENT('https://example.com/resume.pdf') AS content;

• Specify the file format

  SELECT AI_PARSE_DOCUMENT('https://example.com/resume.pdf', 'pdf') AS content;

• Parse an image (such as a scanned resume)

  SELECT AI_PARSE_DOCUMENT('https://example.com/resume_scan.jpg') AS content;

• Feed the parsed result into a processing pipeline (parse -> extract -> summarize)

  SELECT
      AI_PARSE_DOCUMENT('https://example.com/cv.pdf') AS raw_text,
      AI_EXTRACT(
          AI_PARSE_DOCUMENT('https://example.com/cv.pdf'),
          '{"name":"full name","skills":"skills"}'
      ) AS structured,
      AI_SUMMARIZE(
          AI_PARSE_DOCUMENT('https://example.com/cv.pdf'), 200
      ) AS summary;

  Note

  Optimization tip: The preceding statement calls AI_PARSE_DOCUMENT repeatedly for each function. We recommend that you use a temporary table or a subquery to cache the parsing result to avoid duplicate API calls.

AI_VL_EMBEDDING

Generates multimodal vectors for an image URL, a video URL, or plain text. Supports image-text hybrid semantic search. The function automatically detects the input type: an input starting with http:// or https:// is processed as an image or video, and other inputs are processed as text. You can also specify the type explicitly through options.

Supported content types

• Text: Any non-URL string. It is automatically recognized as text.

• Image: jpg, jpeg, png, webp, bmp, tiff, tif, ico, dib, icns, and sgi.

• Video: mp4, avi, and mov.

• Base64 Data URI: data:image/... or data:video/....

Syntax

SELECT AI_VL_EMBEDDING(content [, model [, options]])

Parameters

• content: Required. A text string, an image/video URL, or a Base64 Data URI.

• model: Optional. The name of the model used by the AI Function. If omitted, the default VL_EMBEDDING model configured in the AI gateway is used. If explicitly specified, the model name must come from the results of SHOW AI MODEL.

• options: Optional. A JSON string.

  Field	Type	Description
  content_type	STRING	Explicitly specifies the content type: "text", "image", or "video". If not specified, the type is auto-detected.
  dimension	INT	The vector dimension. By default, it is determined by the model.
  fps	DOUBLE	The frame sampling rate of the video. Effective only for videos.

Return value

Returns an array of floating-point numbers of the JSON type.

Examples

• Generate an image vector

  SELECT AI_VL_EMBEDDING('https://example.com/product.jpg') AS vec;

• Generate a text vector (multimodal space)

  SELECT AI_VL_EMBEDDING('red dress') AS vec;

• Generate a video vector with explicit parameters

  SELECT AI_VL_EMBEDDING(
      'https://example.com/clip.mp4',
      '',
      '{"content_type": "video", "dimension": 1024, "fps": 2.0}'
  ) AS vec;

• Image-to-image search

  SELECT id, product_name,
      AI_SIMILARITY(
          AI_VL_EMBEDDING('https://example.com/query.jpg'),
          image_embedding
      ) AS similarity
  FROM products
  WHERE image_embedding IS NOT NULL
  ORDER BY similarity DESC
  LIMIT 10;

• Text-image hybrid search

  -- Compute both text similarity and image similarity with a weighted sum
  SELECT id, product_name,
      (AI_SIMILARITY('red dress', text_embedding) * 0.4 +
       AI_SIMILARITY(AI_VL_EMBEDDING('https://example.com/red_dress.jpg'), image_embedding) * 0.6
      ) AS combined_score
  FROM products
  ORDER BY combined_score DESC
  LIMIT 20;

AI_TEXT2SQL

Converts a natural language query into an executable SQL statement. The function automatically recognizes the table schema of the current database (the database selected by USE database) and submits it together with your natural language description to an LLM to generate SQL. When the database contains many tables, the model first filters the relevant tables and then generates the final SQL based on them.

-- Basic call
SELECT AI_TEXT2SQL(prompt);

-- Specify a model
SELECT AI_TEXT2SQL(prompt, model);

-- Specify a model and generation parameters
SELECT AI_TEXT2SQL(prompt, model, options);

Parameters

• prompt: Required. The natural language query description. Character types (CHAR, VARCHAR, and TEXT) are supported.

• model: Optional. The name of the model used by the AI Function. If omitted, the default LLM model configured in the AI gateway is used. If explicitly specified, the model name must come from the results of SHOW AI MODEL.

• options: Optional. A JSON string that controls the generation behavior of the LLM (temperature, max_tokens, and so on, same as AI_PROMPT).

Return value

• Returns the TEXT type, which is the SQL statement generated by the model.

• An error is returned if the prompt parameter is NULL or an empty string ("").

Examples

• Basic query generation

  -- Call the function after switching to the target database
  USE my_database;
  
  SELECT AI_TEXT2SQL('Find all users older than 30');

  A similar result is returned:

  AI_TEXT2SQL
  -----------
  SELECT * FROM users WHERE age > 30;

• Cross-table aggregation query

  SELECT AI_TEXT2SQL('Count the number of employees and the average salary for each department');

• Mixed Chinese and English query

  SELECT AI_TEXT2SQL('Find the top 10 best-selling products in the last 7 days');

• Specify the model and temperature

  SELECT AI_TEXT2SQL(
      'Find the top 5 products by sales',
      '', -- An empty string means the default model is used
      '{"temperature": 0.1, "max_tokens": 500}'
  );

Model management

The models of PolarDB-X AI Functions are fully managed by the AI gateway. After the AI gateway starts, it automatically registers the following types of models and configures the optimal default model for each AI Function:

View available models

View model details

View AI function configurations

-- View all AI functions
SHOW AI FUNCTION;

-- View a specified function
SHOW AI FUNCTION FROM AI_PROMPT;

Change the default model of an AI function

The AI gateway configures a recommended default model for each AI Function. To switch the default model used by a function, for example, to make AI_PROMPT use a more powerful model, you can modify it as follows.

SELECT AI_UPDATE_FUNCTION(function_name, model_name);

Specify the model for an AI Function call

The AI gateway has already bound the optimal default model to each AI Function, so you do not need to specify a model name when you call a function. To use another available model, explicitly pass, via the model parameter of the corresponding function, a model name returned by SHOW AI MODEL. The position of the model parameter may differ across functions.

-- Use the default model (recommended, out of the box)
SELECT AI_PROMPT('Explain what a distributed database is in one sentence');

-- Explicitly specify another available model
SELECT AI_PROMPT('Explain what a distributed database is in one sentence', '<model_name>');

Two-stage semantic search (vector recall + cross-encoder reranking)

When you build enterprise semantic search, the recall accuracy of single vector similarity is limited. We recommend the two-stage approach of vector index recall + reranking: first recall the top-N candidates by using an HNSW vector index, then use AI_RANK to score the candidates with high precision, and finally output the most relevant results.

-- 1. Offline indexing: compute and store vectors in batches
UPDATE resumes
SET embedding = VEC_FROMTEXT(AI_EMBEDDING(raw_text))
WHERE embedding IS NULL;

-- 2. Online search: generate the query vector only once
SET @query_embedding = AI_EMBEDDING('Senior Java backend engineer');

-- 3. Recall the top 20 candidates by vector index, then call AI_RANK for reranking
SELECT id, name, summary, vector_distance,
    AI_RANK('Senior Java backend engineer', summary) AS rank_score
FROM (
    SELECT id, name, summary,
        VEC_DISTANCE_COSINE(embedding, VEC_FROMTEXT(@query_embedding)) AS vector_distance
    FROM resumes
    WHERE embedding IS NOT NULL AND summary IS NOT NULL
    ORDER BY vector_distance ASC
    LIMIT 20
) recalled
ORDER BY rank_score DESC;

Key points

• The embedding column should be a VECTOR column with a dimension consistent with the model output, and an HNSW vector index should be created on it.

• The query vector is generated only once. The recall stage does not call the Embedding API, and AI_RANK reranks only the top 20 candidates, so the overall latency is controllable.

Pipeline from unstructured documents to structured data

By chaining multiple AI Functions such as parsing, extraction, summarization, classification, and embedding, you can convert unstructured data such as PDFs and images into analyzable structured data and store it in the database. Document parsing is a remote model call. Make sure each document is parsed only once to avoid duplicate calls.

-- Parse each document only once in the subquery, then perform extraction, summarization, classification, and embedding on the parsed result
INSERT INTO resumes (raw_text, structured_info, summary, category, embedding)
SELECT
    raw_text,
    AI_EXTRACT(
        raw_text,
        '{"name":"full name","skills":"skills","experience_years":"years of experience"}'
    ) AS structured_info,
    AI_SUMMARIZE(raw_text, 200) AS summary,
    AI_CLASSIFY(
        raw_text,
        '["frontend", "backend", "algorithm", "data", "operations", "other"]'
    ) AS category,
    VEC_FROMTEXT(AI_EMBEDDING(raw_text)) AS embedding
FROM (
    SELECT AI_PARSE_DOCUMENT(file_url) AS raw_text
    FROM file_inbox
    WHERE status = 'pending'
) parsed;

Multimodal product search (image-to-image search + text-image hybrid search)

With AI_VL_EMBEDDING, images and text can be represented in a unified multimodal semantic space, so you can build a product search system that integrates image and text search.

1. Product ingestion: Store both multimodal vectors and pure text vectors

   INSERT INTO products (name, image_url, text_embedding, image_embedding)
   VALUES (
       ?,
       ?,
       AI_EMBEDDING(?),                       -- Pure text vector, used for text-to-text search
       AI_VL_EMBEDDING(?)                     -- Multimodal vector, used for text-image hybrid search
   );

2. Image-to-image search

   SET @query_image_embedding = AI_VL_EMBEDDING('https://example.com/query.jpg');
   
   SELECT id, product_name,
       AI_SIMILARITY(
           @query_image_embedding,
           image_embedding
       ) AS similarity
   FROM products
   WHERE image_embedding IS NOT NULL
   ORDER BY similarity DESC
   LIMIT 10;

3. Text-image hybrid search: weighted fusion of text similarity and image similarity

   SET @query_text_embedding = AI_EMBEDDING('red dress');
   SET @query_image_embedding = AI_VL_EMBEDDING('https://example.com/red_dress.jpg');
   
   SELECT id, product_name,
       (AI_SIMILARITY(@query_text_embedding, text_embedding) * 0.4 +
        AI_SIMILARITY(@query_image_embedding, image_embedding) * 0.6
       ) AS combined_score
   FROM products
   ORDER BY combined_score DESC
   LIMIT 20;

Key points

• AI_VL_EMBEDDING and AI_EMBEDDING output vectors in different semantic spaces. They must not be mixed across functions. We recommend that you store text_embedding and image_embedding separately in the table.

• The weighting factors can be tuned based on business effect. For example, set the image weight to 0.7 or higher for image-centric scenarios, and set the text weight to 0.7 or higher for text-centric scenarios.

Batch data governance (classification, masking, cleansing)

By leveraging the batch invocation capability of AI Functions, you can perform semantic-level governance directly on existing data within SQL, without writing additional ETL programs.

• Batch classification

  UPDATE feedback
  SET sentiment = AI_CLASSIFY(content, '["positive", "negative", "neutral"]')
  WHERE sentiment IS NULL;

• Batch structured extraction

  UPDATE orders
  SET parsed_info = AI_EXTRACT(
      order_notes,
      '{"customer_name":"customer name","delivery_address":"delivery address","special_requirements":"special requirements"}'
  )
  WHERE order_notes IS NOT NULL AND parsed_info IS NULL;

• Batch summarization

  UPDATE articles
  SET summary = AI_SUMMARIZE(content, 200)
  WHERE summary IS NULL;

Key points

• We recommend that you run UPDATE in batches, for example by primary key range, to avoid scanning a very large table at once, which causes a single SQL statement to occupy resources for a long time.

• For latency-sensitive online scenarios, we recommend that you precompute results such as vectors and summaries, store them in the database, and reuse them directly at query time.