1.4 What Is New in MySQL 9.0

This section summarizes what has been added to, deprecated in, changed, and removed from MySQL 9.0 since MySQL 8.4. A companion section lists MySQL server options and variables that have been added, deprecated, or removed in MySQL 9.0; see Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 9.0 since 8.4”.

• Features Added or Changed in MySQL 9.0

• Features Deprecated in MySQL 9.0

• Features Removed in MySQL 9.0

Features Added or Changed in MySQL 9.0

The following features have been added to MySQL 9.0:

• JavaScript stored programs.  MySQL Enterprise Edition now includes support for stored programs written in JavaScript, such as this simple example created using the CREATE FUNCTION statement and JavaScript code shown here:

  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$
  ;

  See Section 27.3.1, “JavaScript Stored Program Creation and Management”, which describes the basics of creation and execution of JavaScript stored programs.

  Support is included for both stored procedures and stored functions written in JavaScript, and is provided by the Multilingual Engine Component (MLE). For more information about determining whether your distribution includes this component, and enabling it, see Section 7.5.6, “Multilingual Engine Component (MLE)”.

  JavaScript language support in MySQL conforms to the ECMAScript 2023 Specification, and uses strict mode by default. Strict mode cannot be disabled. This implementation includes all of the standard ECMAScript library objects such as Object, Function, Math, Date, String, and so forth. console.log() and console.error() are also supported (see Section 27.3.9, “JavaScript Stored Program Examples”).

  Most MySQL data types are supported for JavaScript stored program input and output arguments, as well as for return data types. Strings must use the utf8mb4 character set. MySQL BLOB and TEXT types are supported, as are many MySQL temporal types. JSON is also supported. The VECTOR type is not supported by the MLE component or by JavaScript stored programs. for more information, see Section 27.3.4, “JavaScript Stored Program Data Types and Argument Handling”, and Section 27.3.8, “JavaScript Stored Program Limitations and Restrictions”.

  Stored programs written in JavaScript support an API for executing and retrieving results from SQL statements, provided by the MLE component. See Section 27.3.6, “JavaScript SQL API”, and Section 27.3.7, “Using the JavaScript SQL API”, for more information. Prepared statements are also supported in JavaScript stored programs; see Section 27.3.7.2, “Prepared Statements”.

  The MLE component provides a number of session information and management functions including mle_session_state() and mle_session_reset(). Section 7.5.6.1, “MLE Component Option and Variable reference”, and Section 7.5.6.2, “MLE Component Status and Session Information”, provide information about MLE configuration options and status variables; see also Section 27.3.5, “JavaScript Stored Programs—Session Information and Options”.

  For general information about JavaScript stored programs, see Section 27.3, “JavaScript Stored Programs”.

• VECTOR type support.  MySQL 9.0 supports a VECTOR column type. A vector is a data structure which consists of a list of entries (4-byte floating-point values) which can be expressed either as a binary string value or a list-formatted string. A VECTOR column is declared with a maximum length or number of entries (in parentheses); the default is 2048, and the maximum is 16383.

  You can create InnoDB tables with VECTOR columns using CREATE TABLE as shown here:

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

  Vector columns are subject to restrictions, some of which are listed here:

  • A VECTOR column cannot be used as any type of key. This includes primary keys, foreign keys, unique keys, and partitioning keys.

  • Some types of MySQL functions and operators do not accept vectors as arguments. These include but are not limited to numeric functions and operators, 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 complete information about these, see VECTOR Supported and Unsupported Functions.

  • A VECTOR cannot be compared with any other type, and can be compared with another VECTOR only for equality.

  • VECTOR columns are currently not supported for NDB tables.

  VECTOR_DIM() (also added in MySQL 9.0) returns the length of a vector. Functions to convert between representations are available. STRING_TO_VECTOR() (alias: TO_VECTOR()) takes a list-formatted representation of a vector and returns the binary string representation; VECTOR_TO_STRING() (alias: FROM_VECTOR()) performs the inverse, as shown here:

  mysql> SELECT STRING_TO_VECTOR('[2, 3, 5, 7]');
  +------------------------------------------------------+
  | TO_VECTOR('[2, 3, 5, 7]')                            |
  +------------------------------------------------------+
  | 0x00000040000040400000A0400000E040                   |
  +------------------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT VECTOR_TO_STRING(0x00000040000040400000A0400000E040);
  +------------------------------------------------------+
  | VECTOR_TO_STRING(0x00000040000040400000A0400000E040) |
  +------------------------------------------------------+
  | [2.00000e+00,3.00000e+00,5.00000e+00,7.00000e+00]    |
  +------------------------------------------------------+
  1 row in set (0.00 sec)

  For more information and examples, see Section 13.3.5, “The VECTOR Type”, and Section 14.21, “Vector Functions”.

• Inline and implicit foreign key constraints.  MySQL now enforces inline foreign key specifications, which were previously accepted by the parser, but ignored. MySQL 9.0 also accepts implicit references to parent table primary key columns.

  Consider the parent table person created by the following statement:

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

  To create a table shirt having a foreign key owner on person, MySQL now accepts and handles correctly any of the CREATE TABLE statements shown here according to the standard:

  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
  );

  In previous versions of MySQL, only the first of the statements just shown had the effect of creating a foreign key.

  For more information, see Section 15.1.20.5, “FOREIGN KEY Constraints”.

• Saving JSON output from EXPLAIN ANALYZE INTO.  Support is now provided for saving JSON output from EXPLAIN ANALYZE into a user variable, using the synatx shown here:

  EXPLAIN ANALYZE FORMAT=JSON INTO @variable select_stmt

  The variable can be used subsequently as a JSON argument to any of MySQL's JSON functions (see Section 14.17, “JSON Functions”). The INTO clause is supported only with FORMAT=JSON; FORMAT must be specified explicitly. This form of EXPLAIN ANALYZE also supports an optional FOR SCHEMA or FOR DATABASE clause.

  Note

  This feature is available only if the explain_json_format_version server system variable is set to 2; otherwise, attempting to make use of it raises ER_EXPLAIN_ANALYZE_JSON_FORMAT_VERSION_NOT_SUPPORTED (EXPLAIN ANALYZE does not support FORMAT=JSON with explain_json_format_version=1).

  See Obtaining Execution Plan Information, for additional information and examples.

• Event DDL in prepared statements.  Beginning with MySQL 9.0.0, the following statements can be prepared:

  • CREATE EVENT

  • ALTER EVENT

  • DROP EVENT

  Positional parameters (? placeholders) are not supported for these statements; you must assemble the text of the statement to be prepared from some combination of string literals, system variables, and user variables. One way to accomplish this in a reusable fashion is to assemble the text of, for instance, a CREATE EVENT statement in the body of a stored procedure, passing any variable parts of the statement as IN parameters to the stored procedure; prepare the assembled text with PREPARE; invoke the procedure using the desired parameter values. See PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements, and SQL Syntax Permitted in Prepared Statements, for more information. See Section 15.1.13, “CREATE EVENT Statement”, for an example.

• Performance Schema system variable tables.  MySQL 9.0 add two new tables to the Performance Schema that provide information about server system variables. These tables are listed here:

  • The variables_metadata table provides general information about system variables. This information includes the name, scope, type, range (where applicable), and description of each system variable recognized by the MySQL server.

    Two of the columns in this table (MIN_VALUE and MAX_VALUE) are intended to replace deprecated columns of the variables_info table.

  • The global_variable_attributes table provides information about attribute-value pairs assigned by the server to global system variables.

  For more information, see Section 29.12.14, “Performance Schema System Variable Tables”.

• EXPLAIN FORMAT=JSON enhancements.  The output of EXPLAIN FORMAT=JSON now includes information about join columns.

• Correlated subquery with LIMIT 1.  Previously, in order to be eligible for transformation to an outer left join with a derived table, a subquery could not contain a LIMIT clause. In MySQL 9.0, this restriction is relaxed slightly, so that a subquery containing LIMIT 1 can now be transformed in this way.

  The LIMIT clause must use only a literal 1. If the LIMIT clause contains any other value, or if it uses a placeholder (?) or variable, the subquery cannot be optimized using the subquery-to-derived transformation.

  See Section 15.2.15.7, “Correlated Subqueries”, for more information.

Features Deprecated in MySQL 9.0

The following features are deprecated in MySQL 9.0 and may be removed in a future series. Where alternatives are shown, applications should be updated to use them.

For applications that use features deprecated in MySQL 9.0 that have been removed in a later MySQL version, statements may fail when replicated from a MySQL 9.0 source to a replica running a later version, or may have different effects on source and replica. To avoid such problems, applications that use features deprecated in 9.0 should be revised to avoid them and use alternatives when possible.

• Performance Schema variables_info table columns.  The MIN_VALUE and MAX_VALUE columns of the Performance Schema variables_info table are now deprecated, and subject to removal in a future MySQL release. Instead, use the columns of the variables_metadata table (see Features Added or Changed in MySQL 9.0) which have the same names.

• Transactions updating transactional and nontransactional tables.  MySQL 9.0.0 deprecates transactions which update transactional tables as well as as tables which are nontransactional or noncomposable. Such a transaction now causes a deprecation warning to be written to both the client and the error log. Only the InnoDB and BLACKHOLE storage engines are transactional and composable (NDBCLUSTER is transactional but not composable). This means that only the combinations of storage engines shown here do not raise the deprecation warning:

  • InnoDB and BLACKHOLE

  • MyISAM and Merge

  • performance_schema and any other storage engine

  • TempTable and any other storage engine

  See Section 19.5.1.36, “Replication and Transactions”, for more information.

Features Removed in MySQL 9.0

The following items are obsolete and have been removed in MySQL 9.0. Where alternatives are shown, applications should be updated to use them.

For MySQL 8.4 applications that use features removed in MySQL 9.0, statements may fail when replicated from a MySQL 8.4 source to a MySQL 9.0 replica, or may have different effects on source and replica. To avoid such problems, applications that use features removed in MySQL 9.0 should be revised to avoid them and use alternatives when possible.

• 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.

  Due to this change, the following server options and variables have also been removed:

  • The --mysql-native-password server option

  • The --mysql-native-password-proxy-users server option

  • The default_authentication_plugin server system variable

  For backward compatibility, mysql_native_password remains available on the client, so that MySQL 9.0 client programs can connect to earlier versions of the MySQL server. In MySQL 9.0, the MySQL native authentication plugin which was built in to previous releases of client programs has been converted into one which must be loaded at run time.

  For more information, see Section 8.4.1, “Authentication Plugins”.