Output:STRING

Input: one argument of typeVECTOR

Output: a string

Syntax:

vector_to_string(some_embedding)

Output:VECTOR

Input: a string

Syntax:

string_to_vector('some_embedding')

Output: one value of type vector.

Output:REAL

Input: required. Takes two vector values, An optional third string argument indicates the distance measure. Default is `l2_squared_distance. Other options include `cosine_distance` and `dot_product`.

Output: the distance between the two vectors.

For example:

SELECT vector_distance(string_to_vector('[1,-2,3]'), string_to_vector('[1,2,3]'), 'Distance_Measure=dot_product');

Output:REAL

Input: takes two vector values. These can be column names or constants.

Output: the cosine distance between the two vectors.

For example:

SELECT cosine_distance(string_to_vector('[1,2,3]'), string_to_vector('[1,1,1]'));

SELECT id FROM t1 ORDER BY cosine_distance(string_to_vector('[1,2,3]'), embedding_column_name) LIMIT 10;

Output:REAL

Input: takes two vector values. These can be column names or constants.

Output: the dot product of the two vectors.

For example:

SELECT dot_product(string_to_vector('[1,2,3]'), string_to_vector('[1,1,1]'));

SELECT id FROM t1 ORDER BY dot_product(string_to_vector('[1,2,3]'), embbeding_column_name) LIMIT 10;

Output:REAL

Input: takes two vector values. These can be column names or constants.

Output: the L2 squared distance between the two vectors.

For example:

SELECT l2_squared_distance(string_to_vector('[1,2,3]'), string_to_vector('[1,1,1]'));

SELECT id FROM t1 ORDER BY l2_squared_distance(string_to_vector('[1,2,3]'), embbeding_column_name) LIMIT 10;

Output:REAL

Syntax:

approx_distance(embedding_name,
query_vector,
'distance_measure=algorithm_name
[, num_leaves_to_search=value]'

Inputs:

1. embedding_name: A vector embedding column name from the base table.
2. query_vector: A constant of type `VECTOR` which can be (but is not required to be) the output ofstring_to_vector.
3. The comma-separated search string options include the following:
   • distance_measure: required. Uses an algorithm to measure distance between vectors. It uses the following string literals:
     • L2_SQUARED
     • COSINE
     • DOT_PRODUCT

     For example:distance_measure=cosine

   • num_leaves_to_search: optional. Specifies the number of leaves to probe for an ANN vector similarity search. If you don't specify the number of leaves, CloudSQL for MySQL picks a computed value for num_leaves_to_search which can be viewed ininformation_schema.innodb_vector_indexes. The computed number is generally a good starting point with good search quality and performance. It's recommended that you tunenum_leaves_to_search based on your workload and performance or quality trade off.

   For example:

   'distance_measure=dot_product,
num_leaves_to_search=100'

   Required LIMIT value: The specified limit is used as the number of neighbors to return (also known as the top K).

   Output: The approximate distance of the top K closest rows in the base table.

   This function can only be used in theORDER BY orSELECT list.

   For example:

   SELECT id, approx_distance(embedding_column_name
string_to_vector('[1,1,1]'),
'distance_measure=cosine') dist from t1
ORDER BY dist LIMIT 10;

   SELECT id
FROM t1
ORDER BY
approx_distance(
embedding_column_name
string_to_vector('[1,1,1]'),
'distance_measure=dot_product,num_leaves_to_search=100) LIMIT 4;