The complete list of new features in MySQL 8.0

There are over 250 new features in MySQL 8.0. The MySQL Manual is very good, but verbose. This is a list of new features in short bullet form. We have tried very hard to make sure each feature is only mentioned once. Note the similar list for MySQL 5.7.

Please download MySQL 8.0 from dev.mysql.com or from the MySQL  Yum,  APT, or SUSE repositories.

SQL DML

1. Non-recursive CTEs [1]
2. Recursive CTEs [1]
3. Window functions [1]
4. ORDER BY and DISTINCT with ROLLUP [1]
5. LATERAL derived tables [1]
6. Outer table references in derived tables [1]

SQL DDL

1. Instant ADD COLUMN [1]
2. Instant RENAME COLUMN [1]
3. Instant RENAME TABLESPACE [1]
4. RESTART statement [1]
5. SET PERSIST statement [1]
6. RENAME TABLES under LOCK TABLES [1]
7. Option to disallow tables without primary keys [1]
8. Character set conversion as an inplace operation [1 2]
9. CREATE TABLESPACE without DATAFILE clause [1]
10. CREATE RESOURCE GROUP [1 2]
11. ALTER RESOURCE GROUP [1 2]
12. DROP RESOURCE GROUP [1 2]
13. Expressions as DEFAULT values [1]
14. CHECK constraint [1]

Indexes

1. Invisible indexes [1]
2. Descending indexes [1]
3. Functional indexes [1]
4. Index skip scan [1]

Functions

1. New function REGEXP_INSTR [1]
2. New function REGEXP_LIKE [1]
3. New function REGEXP_REPLACE [1]
4. New function REGEXP_SUBSTR [1]
5. New function UUID_TO_BIN [1]
6. New function BIN_TO_UUID [1]
7. New function IS_UUID [1]
8. New function GROUPING [1 2]
9. New function STATEMENT_DIGEST [1]
10. New function STATEMENT_DIGEST_TEXT [1]
11. Bit operations allowed on BINARY, VARBINARY, BLOB, TINYBLOB, MEDIUMBLOB and LONGBLOB [1]

JSON

1. New function JSON_PRETTY [1]
2. New function JSON_STORAGE_SIZE [1]
3. New function JSON_STORAGE_FREE [1]
4. New function JSON_MERGE_PATCH [1]
5. New aggregation and window function JSON_ARRAYAGG [1]
6. New aggregation and window function JSON_OBJECTAGG [1]
7. New table function JSON_TABLE [1]
8. Faster sorting of JSON values [1]
9. Ranges in JSON patch expressions [1 2]
10. In-place updates of JSON values [1 2]
11. JSON Schema validation [1]
12. JSON array indexes [1 2]

GIS

1. Spatial reference systems (SRSs) [1 2 3 4]
2. CREATE SPATIAL REFERENCE SYSTEM statement [1 2]
3. DROP SPATIAL REFERENCE SYSTEM statement [1]
4. SRID type modifier [1 2]
5. Geographic R-trees [1]
6. New setter function ST_SRID(geometry, new_srid) [1]
7. New setter function ST_X(geometry, new_x) [1]
8. New setter function ST_Y(geometry, new_y) [1]
9. New function ST_SwapXY [1]
10. New function ST_Latitude [1]
11. New function ST_Longitude [1]
12. New function ST_Transform [1]
13. Geography support in ST_Distance, ST_Contains, ST_Crosses, ST_Disjoint, ST_Equals, ST_Intersects, ST_Overlaps, ST_Touches, ST_Within, MBRContains, MBRCoveredBy, MBRCovers, MBRDisjoint, MBREquals, MBRIntersects, MBROverlaps, MBRTouches, MBRWithin, ST_IsSimple, ST_IsValid, ST_Length, ST_Validate, ST_Area [1 2 3 4 5 6 7 8]
14. ST_Distance_Sphere for geographic geometries [1]
15. GEOMCOLLECTION as synonym to GEOMETRYCOLLECTION [1 2 3]
16. Optional SPATIAL keyword in R-tree index clauses [1]
17. Ability to specify length unit in ST_Distance() [1]
18. Ability to specify length unit in ST_Length() [1]

Character sets and collations

1. UTF-8 (utf8mb4) as default character set [1 2 3 4 5 6 7 8 9 10 11]
2. General Unicode 9.0 collations covering German (dictionary order), Austrian German (dictionary order), English, French (including accent insensitive Canadian French), Irish, Indonesian, Italian, Luxembourgian, Malay, Dutch (including Flemish), Portuguese (including Brazilian Portuguese), Swahili, and Zulu [1 2]
3. Language specific Unicode 9.0 collations for Czech, Danish (also valid for Norwegian), German (phonebook order), Esperanto, Spanish, Spanish (traditional), Estonian, Croatian (also valid for Serbian with latin characters, and Bosnian), Hungarian, Icelandic, Lithuanian, Latvian, Polish, Romanian, Slovak, Slovenian, Swedish, Turkish, Vietnamese, Japanese (including kana sensitive collation), Chinese, and Russian (also valid for Bulgarian)
4. Unicode support in RLIKE and REGEXP [1]
5. Unicode 9.0 code point order collation (utf8mb4_0900_bin) [1]

Information Schema

1. Information Schema implemented as views over data dictionary tables [1]
2. New Information Schema view VIEW_TABLE_USAGE [1]
3. New Information Schema view VIEW_ROUTINE_USAGE [1]
4. New Information Schema view KEYWORDS [1]
5. New Information Schema view COLUMN_STATISTICS [1]
6. New Information Schema view ST_GEOMETRY_COLUMNS [1]
7. New Information Schema view ST_SPATIAL_REFERENCE_SYSTEMS [1]
8. New Information Schema view ST_UNITS_OF_MEASURE [1]

Performance Schema

1. Performance Schema Indexes [1]
2. Instrument server errors  [1]
3. Statements latency histograms [1]
4. Instrument data locks [1]
5. Pluggable performance schema tables [1]
6. Added QUERY_SAMPLE_TEXT  [1]
7. Added Thread Pool Tables  [1] (Enterprise)

SHOW

1. SHOW now lists hidden columns [1]
2. SHOW now lists index information [1]

Optimizer

1. Histograms [1 2 3]
2. Adaptive scan buffer size [1]
3. IO costs separation between memory and disk [1]
4. Default values in cost tables [1]
5. Sampling interface in storage engine API [1 2 3 4]
6. NOWAIT and SKIP LOCKED [1 2]
7. Avoid unnecesary index dives with FORCE INDEX [1 2 3]
8. Optimizer switch to use invisible indexes [1 2 3]
9. Increased default optimizer trace buffer size [1]
10. New hint MERGE [1]
11. New hint INDEX_MERGE [1]
12. New hint NO_INDEX_MERGE [1]
13. New hint JOIN_FIXED_ORDER [1]
14. New hint JOIN_ORDER [1]
15. New hint JOIN_PREFIX [1]
16. New hint JOIN_SUFFIX [1]
17. New hint SET_VAR [1]
18. Consider covering prefix indexes for LIKE [1]
19. Transformed statement in EXPLAIN of INSERT/UPDATE/REPLACE/DELETE [1]

InnoDB

1. Highly scalable latch free redo log implementation [1 2].
2. Redesign of LOB infrastructure for better performance [1 2 3]
3. State of the art lock scheduler using Contention Aware Transaction Scheduling (CATS) (Contribution from University of Michigan) [1]
4. Infrastructure to do non locking parallel reads (currently used by CHECK TABLE) [1]
5. Instant add column and virtual column [1]
6. Report pages cached in the buffer pool by indexes via the information schema [1]
7. Persistent auto increment [1]
8. Manage UNDO tablespaces using SQL syntax [1]
9. New in-memory temptable storage engine for use by optimiser [1]
10. Support for BLOBs in new temptable engine [1 2]
11. Redo log encryption [1]
12. Undo log encryption [1]
13. General tablespace encryption support [1]
14. Dedicated server mode, automatically configures the buffer pool and redo log size [1 2]
15. Tablespace version support for better upgrade/downgrade experience [1]
16. Self describing tablespaces with Serialized Dictionary Information (SDI) [1]
17. Tools to manage the SDI [ 1]
18. Atomic DDL [1]
19. Remove the buffer pool mutex (Percona contribution) [1]
20. Improved purge [1]
21. Dynamically enable/disable the deadlock detector [1]
22. IO layer is now more scalable and efficient [1]
23. Extended locking semantics with skip and no-waits [1 2]
24. Use the new error logging infra structure [1]
25. System data dictionary is now stored in InnoDB [1]
26. New configuration to generate smaller core files [1]
27. Deprecate Shared tablespaces in partitioned table [1]
28. Reclaim temporary tablespace disk space online [1]

Data Dictionary and Upgrade

1. Transactional Data Dictionary [1]
2. Store all meta data in InnoDB, no FRMs, TRG etc [1,2]
3. Store redundant copy of meta data in SDI [1]
4. Atomic and crash safe DDL [1]
5. Automatic upgrade of dictionary tables, and enhanced checks [1]
6. Automatic upgrade, no need for mysql_upgrade script [1]

Network

1. Support multiple addresses for the –bind-address command option [1]
2. Add Admin Port [1 2 3 4 5]
3. Remove mutex bottlenecks for connect/disconnect performance [1]

Error logging

1. Improved error logging in 8.0 [1]
2. Defaults change: log_error_verbosity=2 [1]
3. Added severity, error code, subsystem to error messages [1]
4. Filtering the error log [1]
5. Error logging in JSON format [1]
6. Force-print specific non-error messages to error log [1]
7. Suppress error logs of type warning or note [1]
8. New “syseventlog” settings [1]
9. Added –log-slow-extra, for richer slow logging [1]

Replication

1. Multi-source Replication Per Channel Filters [1]
2. Atomic DDL Recovery With The Binary Log [1]
3. Write-set Based Transaction Dependency Tracking [1 2 ]
4. Reduced Contention Between Receiver and Applier Threads [1]
5. Binary Log Encryption at Rest [1]
6. GTID Support for Temporary Tables Inside Transactions [1]
7. Partial JSON Update Replication [1]
8. Extended table metadata in the binary log [1 2]
9. RESET MASTER TO ‘x’ [1]
10. Settable GTID_PURGED When GTID_EXECUTED is Not Empty [1]
11. Sub-second Binary Logs Expiration Settings [1]
12. Non-Blocking Replication Monitoring even when Disk is Full [1]
13. Transaction Byte Length Metadata in Binary log [1]
14. Server Versions for each Transaction in the Binary Log  [1, 2, 3]
15. Support for START SLAVE UNTIL for Multi-Threaded Applier [1]
16. Delayed Replication in Microseconds [1]
17. binlog-row-event-max-size system variable [1]
18. PFS: Applier Lag and Queues Monitoring [1 2 3]
19. PFS: Read Consistent Log Positions for Backups [1]
20. PFS: Row-based Replication Applier Thread Progress [1]
21. PFS: Counters for Replication Applier Retries [1]
22. Rotating binary log master key online [1]

Group Replication

1. Transaction Savepoint Support [1]
2. Disallow Writes to Isolated Members in Group Replication [1 2 3]
3. Group-wide Certification and Applier Stats Monitoring [1 2]
4. Options to Fine-tune the Flow-Control [1 2 3 4 5 6 7 8]
5. Support for Hostnames in the Whitelist [1]
6. Shutdown Server When Server Drops Out of the Group [1]
7. Online and User-Triggered Primary Switchover/Election [1 2]
8. Online and User-Triggered Single-to-Multi Primary Switchover [1 2]
9. Configurable Messaging Pipelining [1 2]
10. Relaxed Member Eviction [1]
11. Consistent Reads [1]
12. Consistent Reads on Primary Fail-over [1]
13. IPv6 Support [1 2]
14. Tracing for Message Passing [1]
15. Configure primary failover candidates priority [1 2]
16. PFS: Instrumented Threads [1 2]
17. PFS: Instrumented Mutexes and Condvars [1 2]
18. PFS: Instrumented Memory Used for the Message Cache [1 2]
19. Better support for large transactions in GR [1]
20. Ability to configure the GR communication protocol [1]
21. Changed EXIT STATE ACTION default to READ_ONLY [1]
22. Auto-rejoin of Group Members [1]
23. Option to display passwords as hexadecimal strings [1]

Security – Authentication

1. Caching sha2 authentication plugin  [1]
2. Introduce delays in authentication based on failed logins [1]
3. Password rotation policy enforcement [1]
4. Old password required for SET PASSWORD for some users [1]
5. Support 2 active passwords per user account  [1]
6. Extra authentication to allow SET PERSIST for security sensitive variables  [1]
7. SASL authentication for LDAP on windows   [1]  (Enterprise)
8. Add support for users with multiple LDAP groups  [1] (Enterprise)

Security – Authorization

1. SQL Roles [1]
2. Break the SUPER privilege into dynamic privileges [1]
3. Granting default roles when new users are created [1 2 3]
4. Additional safety to –skip-grant-tables  [1]
5. Secure session variable setting (MYSQL_SESSION_ADMIN privilege) [1]
6. Checking authorization for rolling back XA-transactions [1]
7. Added Partial Revokes [1]
8. Added the SYSTEM_USER Dynamic Privilege [1]

Security – Auditing

1. Audit log: abort queries on rule based conditions   [1] (Enterprise)
2. Audit log: JSON format, compression and encryption  [1]  (Enterprise)
3. Audit log: SQL function to inject data into the Audit log  [1] (Enterprise)
4. Audit log: Multiple encryption passwords [1] (Enterprise)

Security – Other

1. Make ACL statements atomic  [1]
2. Server as a keyring backend migration tool  [1]
3. Migrate away from yaSSL  [1]
4. Support for FIPs enabled OpenSSL library  [1 2]
5. Data masking functions [1] (Enterprise)
6. Ensure foreign key error does not reveal information about parent table [1]
7. Added support for TLS 1.3 [1]
8. Allow switching SSL options for a running server [1]

Router

1. Persist last known metadata-server addresses [1]
2. Reset max_connect_errors on successful connections [1]
3. Build Router as part of the MySQL Server source-tree [1]
4. Added mysqlrouter_plugin_info tool [1]
5. Reduced metadata-cache TTL from 300s to 500ms [1]
6. Added routing strategies [1]
7. Added bootstrap option for –report-host [1]
8. Added bootstrap option for –account-host [1]
9. Disconnect clients to server-nodes that changed from PRIMARY to SECONDARY [1]
10. Log-rotation via SIGHUP [1]
11. HTTP server plugin [1]
12. Flexible support for single/multi-PRIMARY Group Replication [1]

Shell

1. MySQL 8.0 support for InnoDB clusters [1]
2. Remote MySQL server configuration and re-configuration for InnoDB clusters [1]
3. Extended cluster status display, including replication lag times [1]
4. Manual primary switch-over and topology re-configuration in InnoDB clusters [1]
5. Advanced cluster customizations for more use-cases and environments
6. MySQL server upgrade checker [1, 2]
7. Import JSON and JSON serialized BSON data  [1, 2]
8. Updated X DevAPI support
9. Secure password management [1, 2]
10. Display column metadata for query results  [1]
11. Direct command line execution of shell APIs [1]
12. Improved built-in help [1]
13. Screen paging [1]
14. Auto-completion [1]
15. Persisted command history by Shell mode. [1]
16. Custom prompts [1]
17. The \sql shell command also works in JavaScript or Python mode [1]
18. Support for server monitoring through user defined reports and the \show and \watch shell commands [1]
19. Parallel Data Import Utility [1]
20. Support for Shell Extensions [1]
21. Support for Shell Plugins [1 2]
22. Support for different result formats: Table, tabbed, vertical, json/raw, json/pretty [1]

Misc

1. Added mysqld_safe-functionality to server [1 2 3]
2. Defaults change: explicit_defaults_for_timestamp= ON [1]
3. Defaults change: max_error_count=1024 [1]
4. Renamed tx_read_only variable to transaction_read_only [1]
5. Renamed tx_isolation variable to transaction_isolation [1]
6. Defaults change: max_allowed_packet=67108864 [1]
7. Defaults change: event_scheduler=ON [1]
8. Defaults change: back_log=-1 (auto-sized) [1]
9. Defaults change: table_open_cache=4000 [1 2]
10. New Backup Lock [1]
11. Server version stored in InnoDB tablespaces [1]
12. Enable MDL Locking for Recovered and Detached Prepared XA Transactions [1]
13. Support meta data locking for Foreign Keys [1]
14. The –ssl-mode client side option to streamline SSL checking [1]
15. The service registry and the component infrastructure [1]
16. CLI interface to read the replication stream  [1]
17. UDF registration service to allow components to auto-register UDFs  [1]
18. MySQL server strings component service  [1]
19. Keyring plugin for AWS KMS  5.7.19 [1]
20. LDAP authentication plugin (client and server)  5.7.19 [1]
21. Make result set metadata transfer optional  [1]
22. Status variables service for components [1]
23. performance schema instrumentation via a component service [1]
24. System variables service for components  [1]
25. Password validation plugin implemented as a component  [1]
26. Component service to deliver signals to the host application
27. Allow plugins to use prepared statements [1]
28. INSERT/UPDATE/DELETE in query rewrite plugin [1]
29. Dynamic allocation of sort buffer [1]
30. Variable length sort keys for NO PAD collations [1]
31. Faster SELECT COUNT (*) without grouping [1]
32. Source code improvements [1]
33. Small tarball download option [1]
34. CAST to FLOAT and DOUBLE [1]
35. OS user as a connection attribute [1]
36. gtid_purged as a comment in mysqldump files [1]
37. Early plugin load flag [1]
38. Current thread component service [1]
39. Mutex lock order tool [1]

Thank you for using MySQL !