MySQL Versions
Versioning involves assigning unique names or numbers to different stages of a software program as it evolves and is released. Typically, the initial release is labeled as version 1.0. Since there is no universal standard for version numbering, each company develops its own system for assigning these version identifiers. As new features are added, bugs are fixed, and security issues are addressed, the version number is incremented to reflect these updates and improvements.
The most recent supported version of MySQL is v5.8. This version includes numerous important updates, such as new features, feature removals, bug fixes, and security enhancements. It encompasses the release history from MySQL 8.0 to MySQL 8.0.21. MySQL v5.8 has been available since April 2018 and will receive support until April 2026.
When installing MySQL on your system, you need to select both the version and distribution format. MySQL can be installed either as a development release or a General Availability (GA) release. The development release includes the latest features but is not suitable for production environments. On the other hand, the General Availability (GA) release, which is also referred to as the production or stable release, is designed for production use. Therefore, you should choose the most recent GA release for a reliable and stable production setup.
Let's explore the new features and updates introduced in the MySQL 8.0 series.
New Features Introduced in MySQL 8.0
MySQL 8.0 introduced several significant features and improvements. Here are some of the key enhancements:
1. Improved JSON Support:
Enhanced JSON functions and indexing capabilities, allowing for more efficient querying and manipulation of JSON data.
2. Window Functions:
Support for window functions, which enable advanced analytics and complex queries involving partitioning and ordering of data.
3. Common Table Expressions (CTEs):
The addition of CTEs, including recursive CTEs, facilitates more readable and maintainable SQL queries.
4. UTF-8mb4 by Default:
UTF-8mb4 is now the default character set, ensuring full support for all Unicode characters, including emojis.
5. InnoDB Enhancements:
Improved InnoDB storage engine features, including better performance, crash recovery, and support for large tablespaces.
6. Invisible Indexes:
Introduction of invisible indexes, which can be used to test performance impacts of index changes without removing the index.
7. Histograms:
Support for histograms, which provide better statistics for the optimizer to improve query performance.
8.Roles:
Implementation of roles for easier management of user privileges, allowing for more flexible and organized security policies.
9. Performance Schema Improvements:
Expanded Performance Schema with more detailed and customizable monitoring capabilities.
10. Default Authentication Plugin:
Introduction of `caching_sha2_password` as the default authentication plugin, providing improved security over the previous `mysql_native_password`.
11.Replication Enhancements:
Various improvements to replication, including support for replication of `WINDOW` functions and better handling of binary log formats.
12.Data Dictionary:
Introduction of a transactional data dictionary, replacing the older metadata storage, enhancing consistency and performance.
These features collectively contribute to greater flexibility, performance, and ease of use in MySQL 8.0
Some Other Important Features
- Enhanced Regular Expressions
- Updated Error Logging using MySQL architecture
- New Backup Lock feature for better DML handling
- Improved Connection Management with specific TCP/IP port configuration and better compression
- Plugins Now Require C++(previously C/C++)
- New Clone Plugins for InnoDB data and replication
- Better Time Zone Support for TIMESTAMP and DATETIME
- SQL Standard Enhancements including table value constructor and explicit table clauses
Features Deprecated in MySQL 8.0
In MySQL 8.0, several features have been deprecated and may be removed in future versions. Here’s a summary:
- utf8mb3 Character Set: Marked as deprecated.
- sha256_password Authentication: Deprecated; future default will be caching_sha2_password.
- validate_password Plugin: Set for deprecation and removal in upcoming releases.
- ENGINE Clause: Deprecated for ALTER TABLESPACE and DROP TABLESPACE operations.
- AUTO_INCREMENT and UNSIGNED Attributes: Deprecated for FLOAT and DOUBLE column types.
- JSON_MERGE_PRESERVE(): Replaces the JSON_MERGE() function.
- SQL_CALC_FOUND_ROWS Modifier and Related Functions: Deprecated, including FOUND_ROWS(), --no--dd--upgrade server option, mysql_upgrade client, and mysql_upgrade_info.
- MYSQL_PWD Environment Variable: Deprecated for specifying the MySQL password.
Features Removed in MySQL 8.0
In MySQL 8.0, several changes were made regarding the InnoDB system tables and views. The following INFORMATION_SCHEMA views that were based on InnoDB system tables have been removed and replaced by new internal system views with updated names:
