MySQL 9.0: JavaScript commands and a new vector type

New versions of the MySQL database belonging to Oracle have been released: 9.0.0 (Innovation Release), 8.4.1 (Long Term Support) and 8.0.38 (Long Term Support). While the Long Term Support versions are designed for stability, Innovation Releases offer new functions, in particular Stored JavaScript and the Vector type in the case of 9.0.

The new Stored JavaScript is only available in the Enterprise Edition and is based on the Multilingual Engine (MLE), which opens up the database for other languages beyond SQL. ECMAScript 2023 is permitted in strict form with all common objects such as Object, Function, Math, Date, String and also JSON, but not the new vector type. Console outputs via console.log() and console.error() are also provided. Strings must be in utf8mb4 format. An example of stored JavaScript:

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

The new type: Vector

Vector databases are particularly popular with developers for AI models. Developers can now also map this in MySQL with the column type VECTOR, which contains a list of 4-byte floating point values. It can be expressed as a string in either binary or list form. Example:

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

New functions for working with vectors are in particular STRING_TO_VECTOR() and VECTOR_TO_STRING():

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)

However, the vector type has a number of restrictions: It cannot accept certain keys, including the primary. Certain functions are not permitted: numeric, full-text search, XML and JSON functions. String and encryption functions are also limited. And it cannot be compared with other types.

Other minor changes in version 9.0 are that EXPLAIN ANALYZE INTO now accepts JSON as a format or extensions to prepared statements. Some things are also deprecated and should no longer be used, for example the MIN_VALUE and MAX_VALUE columns of the variables_info table. Oracle has completely removed the mysql_native_password plug-in.

Further information on version 9.0 can be found in the documentation and on the other new versions in the blog.

(who)