Location>code7788 >text

3 New Features of MySQL9

Popularity：869 ℃/2024-10-08 11:41:09

This article explains 3 new features of MySQL 9: support for saving JSON output to user variables, support for prepared statements, and support for AI-oriented vector storage.

17.12 MySQL9 New Feature 1 - Support for Saving JSON Output to User Variables

Starting from MySQL 9 version supports saving the JSON output of EXPLAIN FORMAT to user variables, here is a case study to understand the new feature.

Create the demo data table tb with the following SQL statement:

CREATE TABLE tb
(
id      INT,
name   VARCHAR(25)
);

Insert the demo data with the following SQL statement:

INSERT INTO tb (id ,name) VALUES (1,'TV sets') ,(2,'refrigeration');

Save the JSON output of the EXPLAIN FORMAT to the user variable @myvariable with the following SQL statement:

EXPLAIN FORMAT =JSON INTO @myvariable

UPDATE tb SET name = "dehumidifiers" WHERE id =2;

 

Check the user variable @myvariable, which is executed as follows:

mysql> SELECT @myvariable\G

*************************** 1. row ***************************

@myvariable: {

  "query_block": {

    "select_id": 1,

    "table": {

      "update": true,

      "table_name": "tb",

      "access_type": "ALL",

      "rows_examined_per_scan": 2,

      "filtered": "100.00",

      "attached_condition": "(`test_db`.`tb`.`id` = 2)"

    }

  }

}

 

17.13 MySQL9 New Feature 2 - Support for Prepared Statements

MySQL 9.0 supports Prepared Statements, which are pre-compiled SQL statements that can contain placeholders that are replaced with actual values when executed. The advantages of prepared statements are as follows:

(1) Prepared statements improve performance because SQL parsing and compilation occurs only once and can then be executed multiple times, each time with different parameters.

(2) Prepared statements enhance application security because they help prevent SQL injection attacks.

Here's a case study to understand how the prepare statement is used.

Use the PREPARE statement to prepare an SQL statement template that can contain one or more placeholders (?) . 

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt1 FROM @s;

 

Use the SET statement to set specific values for placeholders.

mysql> SET @a = 6;
mysql> SET @b = 8;

Use the EXECUTE statement to execute the prepared SQL statement and replace the placeholders with the previously set parameters. 

mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|       10   |
+------------+

When execution is complete, use the DEALLOCATE PREPARE statement to release the prepared statement. 

mysql> DEALLOCATE PREPARE stmt1;

Prepare statements can also be used to dynamically select the table to be queried, by making the table name a user variable and replacing it in the SQL statement at execution time. For example, by setting the value of the user variable @table to the table name, then constructing a SQL query string containing that table name, and finally executing that query using the PREPARE and EXECUTE statements. This flexibility makes prepare statements useful when dealing with dynamic SQL scenarios.

7.14 MySQL9 New Feature 3 - Support for AI-Oriented Vector Storage

MySQL 9.0 adds a new vector data type, VECTOR, which is a data structure (array) that can store N data items in the following syntax format:

VECTOR(N)

where each data item is a 4-byte single-precision floating-point number. The default number of data items is 2048 and the maximum value is 16383.

Data of vector type can be represented using binary strings or list-separated strings, for example:

CREATE TABLE mytb1(id int, rgb vector(3));
INSERT INTO mytb1 VALUES (1, to_vector('[255,255,255]'));
INSERT INTO mytb1 VALUES (2, to_vector('[128,255,0]'));
INSERT INTO mytb1 VALUES (3, to_vector('[0,65,225]'));

MySQL 9.0 also adds some vector functions for manipulating VECTOR data.

1. STRING_TO_VECTOR function

STRING_TO_VECTOR function is used to convert vector data in the form of a string to binary, the STRING_TO_VECTOR function's parameter is a string containing a set of floating-point numbers separated by commas and referenced using square brackets ([]). For example:

mysql> SELECT HEX(STRING_TO_VECTOR("[1.08, -18.8, 88]"));
+--------------------------------------------+
| HEX(STRING_TO_VECTOR("[1.08, -18.8, 88]")) |
+--------------------------------------------+
| 713D8A3F666696C10000B042                   |
+--------------------------------------------+

2. VECTOR_TO_STRING function

The VECTOR_TO_STRING function is used to convert vector data to a string. Example:

mysql> SELECT VECTOR_TO_STRING(STRING_TO_VECTOR("[1.08, -18.8, 88]"));
+---------------------------------------------------------+
| VECTOR_TO_STRING(STRING_TO_VECTOR("[1.08, -18.8, 88]")) |
+---------------------------------------------------------+
| [1.08000e+00,-1.88000e+01,8.80000e+01]                  |
+---------------------------------------------------------+

mysql> SELECT VECTOR_TO_STRING(0x00000040000040444000A0400000E400);
+------------------------------------------------------+
| VECTOR_TO_STRING(0x00000040000040444000A0400000E400) |
+------------------------------------------------------+
| [2.00000e+00,7.68000e+02,5.00003e+00,2.09385e-38]    |
+------------------------------------------------------+

 

Floating point numbers in the output are expressed using scientific notation.

3. VECTOR_DIM function

The VECTOR_DIM function is used to return the dimension of the vector data, that is, the number of data items. Example:

mysql> SELECT VECTOR_DIM(rgb) FROM mytb1;
+-----------------+
| VECTOR_DIM(rgb) |
+-----------------+
|            3    |
|            3    |
|            3    |
+-----------------+

 

<Previous Next>