DEVELOPER

JavaScript instead of SQL: MySQL 9.0 allows new languages

04/07/2024

New versions of the MySQL database, which belongs 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 variants are designed for stability, the Innovation Release 9.0 introduces new functions, these are notably stored JavaScript and; Vector-Type.

New Stored JavaScript Available only in Enterprise edition and it Based on Multi Linguistic Engine (MLE)which opens the database to languages other than SQL. ECMAScript 2023 has been approved in strict form with all common objects Object, Function, Math, Date, String And also JSON, but not the new vector type. A console version is also planned console.log() And console.error().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$
;

New type: Vector

Vector databases are especially popular among developers for AI models. Developers can now do this with column types VECTOR Also map to MySQL, which contains a list of 4 byte floating point values. This can be expressed as a string, in binary or list form. An example:

Beelink’s MiniPC model EQ13, available in our market

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

There are new functions specifically for working with vectors 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 several limitations: It cannot accept some keys, including primary. Some functions are not allowed: numeric, full-text search, XML and JSON functions. String and encryption functions are also limited. And it cannot be compared with others.

Other minor changes from version 9.0 are: EXPLAIN ANALYZE INTO JSON is now accepted as a format or extension for prepared statements. Some things are also old (obsolete) and should not be used anymore, for example MIN_VALUE– And MAX_VALUE-K columns variables_info-Table. Oracle has completely removed the mysql_native_password plug-in.

More information about version 9.0 can be found In the documentation and to Other new versions In the blog.

(Who)