macOS Windows

Changes to MySQL 9 include support for JavaScript, a new type, inline and implicit foreign key constraints, saving JSON output from EXPLAIN ANALYZE INTO, and many more enhancements.

1. Notable new features

JavaScript stored program. MySQL Enterprise Edition now supports stored pprograms written in JavaScript.

VECTOR type. MySQL 9.0 supports the VECTOR column type, which consists of a list of entries that can be expressed as a binary string value or a list formatted string. This allows MySQL to be store vector embedded data, with the caveat that VECTOR columns can’t be used as any type of keys, they can’t be used as arguments in some MySQL functions and vectors, cannot be used in NDB tables and can only be compared with other VECTOR columns.

Inline and implicit foreign key constraints. MySQL now enforces inline foreign key specifications, and now accept implicit references to parent table primary key columns, which should streamline common database operations that require interacting with foreign keys.

Saving JSON output from EXPLAIN ANALYZE INTO. This allows using the JSON output from EXPLAIN ANALYZE as a JSON argument for MySQL’s JSON functions.

Event DDL in prepared statements. CREATE EVENT, ALTER EVENT and DROP EVENT can now be used in prepared statements.

Performance Schema system variable tables. Two new tables have been added to the Performance Schema. The variables_metadata table provide information about system variables. The global_variable_attributes table provide information about attribute-value pairs assigned by the server to global system variables.

EXPLAIN FORMAT=JSON. The output of EXPLAIN FORMAT=JSON now include information about join columns.

Correlated subquery with LIMIT 1. A subquery containing LIMIT 1 can now be transformed to an outer left join with a derived table. The LIMIT clause must only use a literal 1.

2. Feature removed or deprecated

Performance schema variable_info columns. MIN_VALUE and MAX_VALUE in variable_info are now deprecated, in favor of the new tables.

Transactions updating transactional and nontransactional tables. MySQL 9.0 deprecates transactions which update transactional tables as well as as tables which are nontransactional or noncomposable.

mysql_native_password plugin. The mysql_native_password authentication plugin, deprecated in MySQL 8.0, has been removed. The server now rejects mysql_native authentication requests from older client programs which do not have CLIENT_PLUGIN_AUTH capability.