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

#MySQL8.0相对于MySQL5.7新增了250多个新功能

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

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]

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]

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]

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), and Russian (also valid for Bulgarian)
4. Unicode support in RLIKE and REGEXP [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. Redesigned IO layer is now more scalable and efficient also removed .isl files [1]
23. Extended locking semantics to skip locked rows or ignore waits on locked row [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

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]

Network

1. Support multiple addresses for the –bind-address command option [1]
2. Add Admin Port [1 2 3 4 5]
3. Remove the major 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. Specify syslog/eventlog related system variables to component variables [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]

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]

Security

1. Make ACL statements atomic [1]
2. Introduce delays in authentication based on failed logins (also in 5.7.17) [1]
3. SQL Roles [1]
4. Break the SUPER privilege into dynamic privileges [1]
5. Automatic assignment and granting of default roles when new users are created [1 23]
6. Password rotation policy enforcement [1]
7. Caching sha2 authentication plugin: a SHA256 based plugin fast enough to replace mysql_native [1]
8. Additional safety to –skip-grant-tables (enables –skip-networking too) [1]
9. Audit log: abort queries on rule based conditions [1]
10. Server as a keyring backend migration tool (also in 5.7.21) [1]
11. JSON format, compression and encryption for audit log (5.7.21) [1]
12. Migrate away from yaSSL [1]
13. Support for FIPs enabled OpenSSL library [1]
14. Old password required for SET PASSWORD for some users [1]
15. Data masking functions (also in 5.7.24) [1] (Enterprise)
16. SASL authentication for LDAP on windows (also in 5.7.24) [1] (Enterprise)
17. Support 2 active passwords per user account [1]
18. A SQL function to inject data into the Audit log [1]
19. Secure session variable setting (MYSQL_SESSION_ADMIN privilege) [1]
20. Extra authentication to allow SET PERSIST for security sensitive variables [1]
21. Add support for users with multiple LDAP groups (also in 5.7.25) [1]
22. Checking authorization for rolling back XA-transactions [1]
23. Ensure foreign key error does not reveal information about parent table [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]

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 [1]
16. Customizable prompts [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]

Thank you for using MySQL !

转自：MySQL 官方博客

https://mysqlserverteam.com/the-complete-list-of-new-features-in-mysql-8-0/