Mysql 简明教程

Data dictionary − In previous MySQL releases, dictionary data was stored in metadata files and non-transactional tables. MySQL now incorporates a transactional data dictionary that stores information about database objects.

Atomic Data Definition Language(Atomic DDL) statements − An Atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction.

Upgrade procedure − Previously, after installation of a new version of MySQL, the MySQL server automatically upgrades the data dictionary tables at the next startup, after which the DBA is expected to invoke mysql_upgrade manually to upgrade the system tables in the mysql schema, as well as objects in other schemas such as the 'sys' schema and user schemas. As of MySQL 8.0.16, the server also performs the tasks previously handled by mysql_upgrade. In addition, the server updates the contents of the help tables as well. A new --upgrade server option provides control over how the server performs automatic data dictionary and server upgrade operations.

Session Reuse − MySQL Server now supports SSL session reuse by default with a timeout setting to control how long the server maintains a session cache that establishes the period during which a client is permitted to request session reuse for new connections. All MySQL client programs support session reuse. In addition, C applications now can use the C API capabilities to enable session reuse for encrypted connections.

Security and account management − The security is improved greatly and greater DBA flexibility in account management is also enabled.

Resource management − MySQL now supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group.

Table encryption management − Table encryption can now be managed globally by defining and enforcing encryption defaults.

InnoDB enhancements − Several InnoDB enhancements were added, like, auto-increment counter value, index tree corruption, mem-cached plug-in, InnoDB_deadlock_detect, tablespace encryption feature, storage engine, InnoDB_dedicated_server, creating temporary tables in temporary tablespace, zlib library etc.

Character set support − The default character set has changed from latin1 to utf8mb4. The utf8mb4 character set has several new collations, including utf8mb4_ja_0900_as_cs, the first Japanese language-specific collation available for Unicode in MySQL.

JSON enhancements − Several enhancements and additions were made to MySQL’s JSON functionality.

Data type support − MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSON data types, which previously could not be assigned default values at all.

Optimizer − Optimizer is enhanced in various ways as well.

Improved hash join performance − MySQL 8.0.23 reimplements the hash table used for hash joins, resulting in several improvements in hash join performance.

Common table expressions − MySQL now supports common table expressions, both non-recursive and recursive.

Window functions − MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row.

Lateral derived tables − A derived table now may be preceded by the LATERAL keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same FROM clause.

Aliases in single-table DELETE statements − In MySQL 8.0.16 and later, single-table DELETE statements support the use of table aliases.

Regular expression support − Previously, MySQL used the Henry Spencer regular expression library to support regular expression operators.

Internal temporary tables − The TempTable storage engine replaces the MEMORY storage engine as the default engine for in-memory internal temporary tables.

Logging − Logging process is also improved in the newer versions.

The utf8mb3 character set is deprecated. Please use utf8mb4 instead.

ucs2, macroman and macce, dec, hp8 are also deprecated. You should use utf8mb4 instead.

User-defined collations are deprecated.

sha256_password is deprecated.

The plugin form of validate_password is still available, but deprecated.

The ENGINE clause for the ALTER TABLESPACE and DROP TABLESPACE statements is deprecated.

The PAD_CHAR_TO_FULL_LENGTH SQL mode is deprecated.

AUTO_INCREMENT support is deprecated for columns of type FLOAT and DOUBLE (and any synonyms).

The UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms)

FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated.

The ZEROFILL attribute for numeric data types and the display width attribute for integer data types are deprecated.

The BINARY attribute is deprecated. However, the use of BINARY to specify a data type or character set remains unchanged.

ASCII and UNICODE are deprecated (MySQL 8.0.28 and later). Use CHARACTER SET instead, in both cases.