MySql 中文参考指南
1.4 What Is New in MySQL 9.0
本节总结了自 MySQL 8.4 以来对 MySQL 9.0 做出的新增、弃用、更改和移除内容。配套章节列出了 MySQL 9.0 中已新增、已弃用、或已移除的 MySQL 服务器选项和变量;请参阅 Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 9.0 since 8.4”。
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
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.
Multilingual Engine Component(MLE)提供对以 JavaScript 编写的存储过程和存储函数的支持。有关确定分发中是否包含此组件以及如何启用它的详细信息,请参阅 Section 7.5.6, “Multilingual Engine Component (MLE)”。
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)”.
在 MySQL 中对 JavaScript 语言的支持符合 ECMAScript 2023 Specification并强制执行默认的严格模式。不能禁用严格模式。此实现包含所有标准 ECMAScript 库对象,例如 Object、Function、Math、Date、String_等。此外还支持 _console.log()_和 _console.error()(请参阅 Section 27.3.9, “JavaScript Stored Program Examples”)。
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”).
大多数 MySQL 数据类型都受 JavaScript 存储程序输入和输出参数以及返回数据类型的支持。字符串必须使用 utf8mb4 字符集。MySQL BLOB 和 TEXT 类型受支持,许多 MySQL 时间类型也受支持。 JSON 也受支持。MLE 组件或 JavaScript 存储程序不支持 VECTOR 类型。有关更多信息,请参阅 Section 27.3.4, “JavaScript Stored Program Data Types and Argument Handling” 和 Section 27.3.8, “JavaScript Stored Program Limitations and Restrictions” 。
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”.
以 JavaScript 编写的存储的程序支持一个 API 来执行 SQL 语句并从中检索结果,它由 MLE 组件提供。有关详细信息,请参阅 Section 27.3.6, “JavaScript SQL API”和 Section 27.3.7, “Using the JavaScript SQL API”。JavaScript 存储的程序中也支持预处理语句;请参阅 Section 27.3.7.2, “Prepared Statements”。
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”.
MLE 组件提供一些会话信息和管理函数,包括 mle_session_state() 和 mle_session_reset() 。 Section 7.5.6.1, “MLE Component Option and Variable reference” 和 Section 7.5.6.2, “MLE Component Status and Session Information” 提供有关 MLE 配置选项和状态变量的信息,另请参阅 Section 27.3.5, “JavaScript Stored Programs—Session Information and Options” 。
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”.
有关 JavaScript 存储程序的一般信息,请参阅 Section 27.3, “JavaScript Stored Programs”。
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.
你可以使用 CREATE TABLE 创建带有 VECTOR 列的 InnoDB 表,如下所示: mysql> CREATE TABLE v1 (c1 VECTOR(5000)); Query OK, 0 rows affected (0.03 sec) ,向量列受到限制,其中一些限制如下所示:
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:
_VECTOR_列不能用作任何类型的键。这包括主键、外键、唯一键和分区键。
A VECTOR column cannot be used as any type of key. This includes primary keys, foreign keys, unique keys, and partitioning keys.
某些类型的 MySQL 函数和运算符不接受向量作为参数。这些函数和运算符包括但不限于数字函数和运算符、时间函数、全文搜索函数、XML 函数、位函数和 JSON 函数。
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.
向量可与某些(但并非所有)字符串和加密函数一起使用。有关这些函数的更完整信息,请参阅 VECTOR Supported and Unsupported 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.
不能将 _VECTOR_与任何其他类型进行比较,并且只能将其与另一个 _VECTOR_比较是否相等。
A VECTOR cannot be compared with any other type, and can be compared with another VECTOR only for equality.
VECTOR 目前不支持 NDB 表。
VECTOR columns are currently not supported for NDB tables.
VECTOR_DIM() (也在 MySQL 9.0 中添加)返回向量的长度。可以使用转换函数在表示之间进行转换。 STRING_TO_VECTOR() (别名: TO_VECTOR() )采用向量列表格式表示并返回二进制字符串表示; VECTOR_TO_STRING() (别名: FROM_VECTOR() )执行反向操作,如下所示:_mysql> SELECT STRING_TO_VECTOR('[2, 3, 5, 7]');----| TO_VECTOR('[2, 3, 5, 7]') |----| 0x00000040000040400000A0400000E040 |----1 row in set (0.00 sec)----
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) [.iokays-translated-ea89f66ea24212237528e7af42f06479] 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)_有关更多信息和示例,请参阅链接:vector.html[第 13.3.5 节、“VECTOR 类型”] 和链接:vector-functions.html[第 14.21 节、“Vector 函数”]。---- [.iokays-original-ea89f66ea24212237528e7af42f06479] 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”.
-
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.
向量可与某些(但并非所有)字符串和加密函数一起使用。有关这些函数的更完整信息,请参阅 VECTOR Supported and Unsupported 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.
-
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.
考虑以下语句创建的父表 person : CREATE TABLE person ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(60) NOT NULL ); 要创建外键 owner 在 person 上的表 shirt ,MySQL 现在根据标准接受并正确处理此处显示的 CREATE TABLE 语句:_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,
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,
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 (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);_在 MySQL 的早期版本中,仅刚显示的第一个语句具有创建外键的效果。
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.
有关详细信息,请参阅 Section 15.1.20.5, “FOREIGN KEY Constraints”。
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.
注意此功能仅在 explain_json_format_version 服务器系统变量设置为 2 的情况下才可用;否则,尝试使用它会导致 ER_EXPLAIN_ANALYZE_JSON_FORMAT_VERSION_NOT_SUPPORTED (EXPLAIN ANALYZE 不支持 explain_json_format_version=1 时 FORMAT=JSON)。
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).
有关其他信息和示例,请参阅 Obtaining Execution Plan Information。
See Obtaining Execution Plan Information, for additional information and examples.
此功能仅在 explain_json_format_version 服务器系统变量设置为 2 的情况下才可用;否则,尝试使用它会导致 ER_EXPLAIN_ANALYZE_JSON_FORMAT_VERSION_NOT_SUPPORTED (EXPLAIN ANALYZE 不支持 explain_json_format_version=1 时 FORMAT=JSON)。 |
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). |
-
Event DDL in prepared statements. Beginning with MySQL 9.0.0, the following statements can be prepared:
这些语句不支持位置参数( ? 占位符);必须通过字符串文本、系统变量和用户变量的组合组装要准备的语句。以可重用方式完成此操作的一种方法是在存储过程的主体中组装 CREATE EVENT 语句的文本,将语句的任何可变部分作为 IN 参数传递给存储过程;使用 PREPARE 准备组装的文本;使用所需的参数值调用过程。有关更多信息,请参阅 PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements 和 SQL Syntax Permitted in Prepared Statements 。有关示例,请参阅 Section 15.1.13, “CREATE EVENT Statement” 。
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:
variables_metadata 表提供有关系统变量的一般信息。此信息包括 MySQL 服务器识别的每个系统变量的名称、范围、类型、范围(如适用)和说明。
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.
此表中的两列( MIN_VALUE 和 MAX_VALUE )旨在替换 variables_info 表的已弃用列。
Two of the columns in this table (MIN_VALUE and MAX_VALUE) are intended to replace deprecated columns of the variables_info table.
global_variable_attributes 表提供有关服务器分配给全局系统变量的属性值对的信息。
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”.
-
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.
此表中的两列( MIN_VALUE 和 MAX_VALUE )旨在替换 variables_info 表的已弃用列。
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.
-
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.
LIMIT_子句只能使用文字 _1。如果 LIMIT_子句包含任何其他值,或者如果它使用占位符 (?_) 或变量,则无法使用子查询到导出转换来优化子查询。
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.
有关更多信息,请参阅 Section 15.2.15.7, “Correlated Subqueries”。
See Section 15.2.15.7, “Correlated Subqueries”, for more information.
Features Deprecated in MySQL 9.0
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.
对于使用 MySQL 9.0 中已弃用并在 MySQL 后续版本中移除的功能的应用程序,当从 MySQL 9.0 源复制到运行后继版本的副本时,语句可能会失败,或者对源和副本产生不同的影响。为了避免此类问题,应修改使用 9.0 中已弃用功能的应用程序以避免使用它们,并尽可能使用替代方法。
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 和 BLACKHOLE
InnoDB and BLACKHOLE
MyISAM 和 Merge
MyISAM and Merge
_performance_schema_和任何其他存储引擎
performance_schema and any other storage engine
_TempTable_和任何其他存储引擎
TempTable and any other storage engine
See Section 19.5.1.36, “Replication and Transactions”, for more information.
-
InnoDB and BLACKHOLE
-
MyISAM and Merge
-
performance_schema and any other storage engine
-
TempTable and any other storage engine
Features Removed in MySQL 9.0
以下是已过时并在 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.
对于使用 MySQL 9.0 中已移除的功能的 MySQL 8.4 应用程序,从 MySQL 8.4 源复制到 MySQL 9.0 副本时,语句可能会失败,或对源和副本产生不同的影响。为了避免此类问题,应修改使用 MySQL 9.0 中移除的功能的应用程序以避免使用这些功能,并在可能的情况下使用替代方案。
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:
—mysql-native-password 服务器选项
The —mysql-native-password server option
—mysql-native-password-proxy-users 服务器选项
The —mysql-native-password-proxy-users server option
_default_authentication_plugin_服务器系统变量
The default_authentication_plugin server system variable
为了向后兼容,_mysql_native_password_仍然在客户端可用,以便 MySQL 9.0 客户端程序可以连接到早期版本的 MySQL 服务器。在 MySQL 9.0 中,内置于以前版本的客户端程序中的 MySQL 本机验证插件已转换为必须在运行时加载的插件。
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.
有关更多信息,请参阅 Section 8.4.1, “Authentication Plugins”。
For more information, see Section 8.4.1, “Authentication Plugins”.
-
The —mysql-native-password server option
-
The —mysql-native-password-proxy-users server option
-
The default_authentication_plugin server system variable