What’s New In MySQL 9.0

MySQL 9.0 was released in July 2024. Let’s see what’s new.

JavaScript Stored Programs

MySQL Enterprise Edition now supports stored programs written in JavaScript. For example, the following is a simple program created using the CREATE FUNCTION statement with embedded JavaScript code:

CREATE FUNCTION gcd(a INT, b INT) 
RETURNS INT 
NO SQL 
LANGUAGE JAVASCRIPT AS
  $mle$
    let x = Math.abs(a)
    let y = Math.abs(b)
    while(y) {
      var t = y
      y = x % y
      x = t
    }
    return x
  $mle$
;

This is supported by stored procedures and stored functions and is provided by the Multilingual Engine Component (MLE). JS support conforms to the ECMAScript 2023 specification and runs in strict mode.

‍

Most MySQL data types are supported for input and output arguments in JavaScript-stored programs, as well as for return data types. Strings must use the utf8mb4 character set. MySQL's BLOB and TEXT types are compatible, along with many temporal types. JSON is also supported. However, the VECTOR type is not supported by the MLE component or in JavaScript stored programs.

VECTOR Type Support

MySQL 9.0 introduces support for the VECTOR column type, a data structure consisting of a list of entries (4-byte floating-point values). These entries can be represented as either a binary string or a list-formatted string. A VECTOR column is defined with a specified maximum length or number of entries (in parentheses); the default is 2048, with a maximum of 16,383 entries.

You can create vectors like this:

mysql> CREATE TABLE v1 (c1 VECTOR(5000));
Query OK, 0 rows affected (0.03 sec)

Vector columns come with certain limitations, including the following:

‍

• A VECTOR column cannot be used as any type of key, such as primary keys, foreign keys, unique keys, or partitioning keys.
• Many MySQL functions and operators do not accept vectors as arguments. This includes but is not limited to, numeric functions, temporal functions, full-text search functions, XML functions, bit functions, and JSON functions.
• Vectors can be used with some, but not all, string and encryption functions. For more details, refer to the VECTOR Supported and Unsupported Functions documentation.
• A VECTOR cannot be compared with any other data type and can only be compared with another VECTOR for equality.
• VECTOR columns are currently not supported for NDB tables.

Inline And Implicit Foreign Key Constraints

MySQL now enforces inline foreign key specifications, which were previously recognized by the parser but ignored. Additionally, MySQL 9.0 supports implicit references to primary key columns of the parent table.

EXPLAIN ANALYZE INTO With JSON

Support has been added for saving JSON output from EXPLAIN ANALYZE into a user-defined variable.

You can use it like this:

EXPLAIN ANALYZE FORMAT=JSON INTO @variable select_stmt

The variable can later be used as a JSON argument for any of MySQL's JSON functions.

Event DDL In Prepared Statements

Starting with MySQL 9.0.0, the following statements can now be prepared:

• CREATE EVENT
• ALTER EVENT
• DROP EVENT

Positional parameters (? placeholders) are not supported for these statements. Instead, you must build the text of the statement to be prepared using a combination of string literals, system variables, and user variables. One way to achieve this in a reusable manner is to construct the text of a statement, such as CREATE EVENT, within the body of a stored procedure. Any variable parts of the statement can be passed as IN parameters to the stored procedure. The assembled text can then be prepared using the PREPARE statement and executed with the desired parameter values.

Performance Schema System Variable Tables

MySQL 9.0 introduces two new tables in the Performance Schema that offer details about server system variables. These tables are:

• The variables_metadata table provides general information about system variables, including their name, scope, type, range (if applicable), and a description of each variable recognized by the MySQL server. 
• Two columns in this table, MIN_VALUE and MAX_VALUE, are designed to replace the deprecated columns in the variables_info table. 
• The global_variable_attributes table provides information about attribute-value pairs assigned by the server to global system variables.

Correlated Subquery With LIMIT 1

Previously, a subquery had to exclude a LIMIT clause to be eligible for transformation into an outer left join with a derived table. In MySQL 9.0, this restriction has been relaxed slightly, allowing subqueries with a LIMIT 1 clause to undergo this transformation.

However, the LIMIT clause must strictly use the literal value 1. If it contains any other value, or if it includes a placeholder (?) or variable, the subquery is not eligible for the subquery-to-derived transformation.

Summary

MySQL has adopted a three-month release cycle, with major Long-Term Support (LTS) releases every two years. According to Oracle, in October we can expect the release of MySQL 8.4.2 LTS and MySQL 8.0.39, which will include bug fixes and security updates, as well as the MySQL 9.1 Innovation release, introducing new features along with bug and security fixes. This makes MySQL even more suitable for modern cloud-native projects.

‍