Home Tutorial New in MySQL 8.0

New in MySQL 8.0

by MH RISHAD - 10 Jul 2021

This section summarizes what has been added to, deprecated in, and removed from MySQL 8.0. A companion section lists MySQL server options and variables that have been added, deprecated, or removed in MySQL 8.0

Features Added in MySQL 8.0

  • Data dictionary.  MySQL now incorporates a transactional data dictionary that stores information about database objects. In previous MySQL releases, dictionary data was stored in metadata files and nontransactional tables. For more information, see Chapter 14, MySQL Data Dictionary.

  • Atomic data definition statements (Atomic DDL).  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. For more information, see Section 13.1.1, “Atomic Data Definition Statement Support”.

  • 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.

  • Security and account management.  These enhancements were added to improve security and enable greater DBA flexibility in account management


  • 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. Group attributes enable control over its resources, to enable or restrict resource consumption by threads in the group. DBAs can modify these attributes as appropriate for different workloads. Currently, CPU time is a manageable resource, represented by the concept of virtual CPU as a term that includes CPU cores, hyperthreads, hardware threads, and so forth.


  • Table encryption management.  Table encryption can now be managed globally by defining and enforcing encryption defaults. The default_table_encryption variable defines an encryption default for newly created schemas and general tablespace.
  • InnoDB enhancements : Cooming more information about this features 
  • 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. For more information, see Section 10.10.1, “Unicode Character Sets”.

  • JSON enhancements. MySQL's JSON functionality comming with new blogs


  • 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 BLOBTEXTGEOMETRY, and JSON data types, which previously could not be assigned default values at all.
  • Optimizer.  These optimizer enhancements were added:
  • Common table expressions.  MySQL now supports common table expressions, both nonrecursive and recursive. Common table expressions enable use of named temporary result sets, implemented by permitting a WITH clause preceding SELECT statements and certain other statements. 
  • Window functions.  MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row. These include functions such as RANK()LAG(), and NTILE(). In addition, several existing aggregate functions now can be used as window functions (for example, SUM() and AVG()).
  • 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. Lateral derived tables make possible certain SQL operations that cannot be done with nonlateral derived tables or that require less-efficient workarounds.
  • 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 (REGEXPRLIKE). Regular expression support has been reimplemented using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. The REGEXP_LIKE() function performs regular expression matching in the manner of the REGEXP and RLIKE operators, which now are synonyms for that function. In addition, the REGEXP_INSTR()REGEXP_REPLACE(), and REGEXP_SUBSTR() functions are available to find match positions and perform substring substitution and extraction, respectively. 
  • Internal temporary tables.  The TempTable storage engine replaces the MEMORY storage engine as the default engine for in-memory internal temporary tables. The TempTable storage engine provides efficient storage for VARCHAR and VARBINARY columns.
  • Logging.  Error logging was rewritten to use the MySQL component architecture. Traditional error logging is implemented using built-in components, and logging using the system log is implemented as a loadable component. In addition, a loadable JSON log writer is available.
  • Backup lock.  A new type of backup lock permits DML during an online backup while preventing operations that could result in an inconsistent snapshot. 
  • Replication.  The following enhancements have been made to MySQL Replication:

    • MySQL Replication now supports binary logging of partial updates to JSON documents using a compact binary format, saving space in the log over logging complete JSON documents. Such compact logging is done automatically when statement-based logging is in use, and can be enabled by setting the new binlog_row_value_options system variable to PARTIAL_JSON. For more information, see Partial Updates of JSON Values, as well as the description of binlog_row_value_options.

    Connection management.  MySQL Server now permits a TCP/IP port to be configured specifically for administrative connections. This provides an alternative to the single administrative connection that is permitted on the network interfaces used for ordinary connections even when max_connections connections are already established. See Section, “Connection Interfaces”.


    • Plugins.  Previously, MySQL plugins could be written in C or C++. MySQL header files used by plugins now contain C++ code, which means that plugins must be written in C++, not C.

    • C API.  The MySQL C API now supports asynchronous functions for nonblocking communication with the MySQL server. Each function is the asynchronous counterpart to an existing synchronous function. The synchronous functions block if reads from or writes to the server connection must wait. The asynchronous functions enable an application to check whether work on the server connection is ready to proceed. If not, the application can perform other work before checking again later.

    • Additional target types for casts.  The functions CAST() and CONVERT() now support conversions to types DOUBLEFLOAT, and REAL. Added in MySQL 8.0.17. See Section 12.11, “Cast Functions and Operators”.

    • JSON schema validation.  MySQL 8.0.17 adds two functions JSON_SCHEMA_VALID() and JSON_SCHEMA_VALIDATION_REPORT() for validating JSON documents again JSON schemas. JSON_SCHEMA_VALID() returns TRUE (1) if the document validates against the schema and FALSE (0) if it does not. JSON_SCHEMA_VALIDATION_REPORT() returns a JSON document containing detailed information about the results of the validation. The following statements apply to both of these functions:

      • The schema must conform to Draft 4 of the JSON Schema specification.

      • required attributes are supported.

      • External resources and the $ref keyword are not supported.

      • Regular expression patterns are supported; invalid patterns are silently ignored.




      Comming More

      Thank You






Leave a Comment

Required fields are marked *