What’s New in MySQL 9.0? VECTOR Type, Inline FK, JSON EXPLAIN, and More

VECTOR Column Type

MySQL 9.0 adds a VECTOR column type for storing arrays of 4‑byte floating‑point values. Length must be specified in parentheses; default is 2048 and maximum is 16383.

CREATE TABLE v1 (c1 VECTOR(5000));

Restrictions: VECTOR cannot be used as any key (primary, foreign, unique, or partition).

Most built‑in functions and operators do not accept VECTOR arguments (numeric, date/time, full‑text, XML, bit, JSON, etc.). VECTOR can only be compared for equality with another VECTOR.

Utility functions introduced: VECTOR_DIM(v) – returns the dimension (length) of the vector. STRING_TO_VECTOR(str) (alias TO_VECTOR) – converts a list‑style string such as '[2,3,5,7]' to the binary VECTOR representation. VECTOR_TO_STRING(v) (alias FROM_VECTOR) – converts a binary VECTOR back to a list‑style string.

SELECT STRING_TO_VECTOR('[2, 3, 5, 7]');

SELECT VECTOR_TO_STRING(0x00000040000040400000A0400000E040);

Inline and Implicit Foreign‑Key Constraints

MySQL 9.0 enforces inline foreign‑key specifications and allows implicit references to the parent table’s primary key.

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(60) NOT NULL
);

All of the following CREATE TABLE statements for a child table are accepted and create the same foreign key:

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    style ENUM('tee','polo','dress') NOT NULL,
    color ENUM('red','blue','yellow','white','black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL,
    FOREIGN KEY (owner) REFERENCES person (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    style ENUM('tee','polo','dress') NOT NULL,
    color ENUM('red','blue','yellow','white','black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL,
    FOREIGN KEY (owner) REFERENCES person
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    style ENUM('tee','polo','dress') NOT NULL,
    color ENUM('red','blue','yellow','white','black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    style ENUM('tee','polo','dress') NOT NULL,
    color ENUM('red','blue','yellow','white','black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person
);

Saving EXPLAIN ANALYZE JSON Output to a User Variable

MySQL 9.0 allows the JSON result of EXPLAIN ANALYZE to be stored directly into a user variable.

EXPLAIN ANALYZE FORMAT=JSON INTO @plan SELECT ...;

The variable can then be passed to any JSON function. The clause requires FORMAT=JSON and the server variable explain_json_format_version must be set to 2; otherwise the statement raises ER_EXPLAIN_ANALYZE_JSON_FORMAT_VERSION_NOT_SUPPORTED.

Optional FOR SCHEMA or FOR DATABASE clauses are also supported.

Event DDL in Prepared Statements

Since MySQL 9.0.0 the DDL statements CREATE EVENT, ALTER EVENT and DROP EVENT can be prepared. Positional placeholders ( ?) are not supported; the statement text must be assembled from literals, system variables or user variables before calling PREPARE.

Performance‑Schema System Variable Tables

Two new tables provide metadata about server variables: performance_schema.variables_metadata – replaces the deprecated MIN_VALUE and MAX_VALUE columns of variables_info. It contains name, scope, type, possible values and description for each variable. performance_schema.global_variable_attributes – stores attribute‑value pairs for global system variables.

Enhanced EXPLAIN FORMAT=JSON

The JSON output now includes information about join columns, making join predicates easier to analyze.

LIMIT 1 Subqueries

Subqueries that contain a literal LIMIT 1 are now eligible for transformation into derived tables for outer‑join optimization. The limit must be the literal value 1; using a variable, placeholder, or any other value disables the transformation.

Deprecated Features

The MIN_VALUE and MAX_VALUE columns of performance_schema.variables_info are deprecated; use the corresponding columns in variables_metadata instead.

Updating transactional and non‑transactional tables in the same transaction now generates a warning unless the storage engines are compatible (e.g., InnoDB + BLACKHOLE, MyISAM + Merge, performance_schema + any other engine, TempTable + any other engine).

Removed Features

The mysql_native_password authentication plugin has been removed from the server. The server now rejects connections from clients that do not advertise CLIENT_PLUGIN_AUTH.

Corresponding server options --mysql-native-password, --mysql-native-password-proxy-users and the system variable default_authentication_plugin have been removed.

For backward compatibility the plugin name can still be used on the client side, but it must be loaded as a runtime plugin.