intro
Let’s go over everything you need to know about the new MySQL 9 VECTOR data type to efficiently store vector data in your database.
Vector data has become increasingly important with the growth of embeddings for AI and machine learning. To support this, MySQL 9—released in late 2024—introduced a new data type: the VECTOR data type. Enter the MySQL 9 VECTOR world!
In this guide, you will explore everything MySQL 9's VECTOR data type has to offer!
What Is the MySQL 9 VECTOR Data Type?
The VECTOR data type is a new data type introduced in MySQL 9. A vector is a data structure that consists of a list of entries, each storing by a 4-byte floating-point value. These vectors can be represented either as binary strings or as list-formatted strings.
While this new feature does not turn MySQL into a vector database, it opens the door to handling vector and embedding data more efficiently within MySQL.
MySQL 9 VECTOR: Syntax and Restrictions
Now that you know what the MySQL 9 VECTOR data type is, you are ready to learn how to use it, when to use it, and its limitations.
Syntax
To define a VECTOR data type in MySQL 9+, use the following syntax:
1
VECTOR[(N)]
A VECTOR stores up to N single-precision (4-byte) floating-point values. If N is omitted, the default length is 2048. Instead, the maximum allowed value for N is 16,383.
To omit N, write:
1
VECTOR
Keep in mind that using VECTOR() (without an N value) will result in a MySQL syntax error:
1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near')' at line X
Note: VECTOR can be used in queries as any other MySQL data types.
Restrictions and Limitations
As of this writing, the main VECTOR MySQL 9 limitations are:
How to Use VECTOR in a MySQL Database Client
Since MySQL 9 has only recently been released, not all database clients fully support it yet. To use the VECTOR data type in a visual database client, you must choose one that is up-to-date and supports this new feature, such as DbVisualizer.
In particular, DbVisualizer 23.4 (among other new features you can try for free) introduced support for the MySQL 9 VECTOR data type.
Let’s explore how to work with this data type in DbVisualizer!
Complete Example
Assume you have a MySQL table defined as follows, where we add a VECTOR column:
That is the SQL DDL of the table we are going to use in this example:
1
CREATE TABLE
2
my_table
3
(
4
id INT NOT NULL,
5
name VARCHAR(255),
6
vector_data vector(1024),
7
PRIMARY KEY (id)
8
)
9
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_0900_ai_ci;
Note that you could have created the table visually using the "Create Table" DbVisualizer Pro feature:
You can insert new records into the table with the VECTOR column as follows:
1
INSERT INTO my_table (name, vector_data)
2
VALUES ('Example 1', STRING_TO_VECTOR('[8, 9]')),
3
('Example 2', STRING_TO_VECTOR('[0.4, 10, 21]'));
Note the use of the STRING_TO_VECTOR() function introduced in MySQL 9 to handle string-to-binary conversions.
The result of the query will be:
If you get the data on the table, this is what you would see:
As you can see, the VECTOR data is presented as simple string-based vectors (particularly, if you set the useCursorFetch option of the MySQL driver to false).
For in-depth usage scenarios, read the following two guides from the Oracle MySQL Blog:
Conclusion
In this guide, you explored the MySQL 9 VECTOR data type and learned how to use it to store vector data directly in your database. As discussed here, working with this data type becomes much easier with a database client like DbVisualizer.
DbVisualizer is a powerful database client with full support for MySQL 9. It also offers advanced features like query optimization, SQL formatting, and schema visualization with ERD-like diagrams. Try DbVisualizer for free today!
FAQ
When was the MySQL 9 VECTOR data type introduced?
The VECTOR data type was introduced in MySQL 9, which was released on July 1, 2024.
What type of data does VECTOR store in MySQL?
In MySQL 9, the VECTOR(N) type stores up to N single-precision (4-byte) floating-point values. This structure is optimized for handling numerical embeddings, making it ideal for machine learning and similarity search applications.
What is the best data type to store vectors for LLMs in MySQL 9?
The MySQL 9 VECTOR type is specifically designed for storing vector embeddings efficiently. It allows storing up to N floating-point values in a structured format, making it ideal for LLM applications like semantic search and similarity matching.
What are the new MySQL 9 VECTOR functions?
These are the new functions added by MySQL 9 to deal with the VECTOR data type:
For more information, refer to the MySQL 9 VECTOR functions documentation page.
Why use a visual database client to deal with VECTOR data?
Working with VECTOR data in MySQL 9 is much easier with a visual database client like DbVisualizer since a SQL client allows you to interact with data visually and see results instantly. Since VECTOR data is not natively supported by all clients, using an up-to-date tool like DbVisualizer guarantees compatibility. Plus, features like query visualization and SQL formatting make it easier to work with complex data structures. Grab a 21-day free trial today!
