MySQL is a popular open-source relational database management system (RDBMS) that has gone through various versions over the years. As of my last knowledge update in January 2022, here are some notable MySQL versions:
MySQL 5.0: Released in October 2005, this version introduced stored procedures, triggers, views, and cursors.
MySQL 5.1: Released in November 2008, it added performance improvements, the plugin API, and the InnoDB storage engine as the default.
MySQL 5.5: Released in December 2010, it included performance and scalability improvements, semi-synchronous replication, and InnoDB as the default storage engine.
MySQL 5.6: Released in February 2013, this version focused on improving performance, scalability, and manageability. It introduced features like the InnoDB full-text search and online DDL.
MySQL 5.7: Released in October 2015, it brought in more enhancements for performance, security, and manageability. Features included improved JSON support and the introduction of the MySQL Document Store.
MySQL 8.0: Released in April 2018, this version introduced major changes, including a new data dictionary, improved security features, enhanced JSON support, and better performance.
Features Added in MySQL 8.0
MySQL 8.0, released in April 2018, introduced several significant features and improvements over its predecessors. Here are some of the key features added in MySQL 8.0:
Data Dictionary: MySQL 8.0 replaced the traditional MyISAM system tables with a transactional data dictionary stored in InnoDB. This enhances the reliability and manageability of the MySQL metadata.
Roles: MySQL 8.0 introduced a native role-based access control (RBAC) system. Users can now define roles and assign privileges to them, simplifying user management and access control.
Window Functions: The introduction of window functions allows for more advanced analytics and reporting queries. These functions operate on a set of rows related to the current row, enabling calculations over a specified range.
Common Table Expressions (CTEs): MySQL 8.0 added support for CTEs, making complex queries more readable and maintainable by allowing the creation of temporary result sets within a SELECT, INSERT, UPDATE, or DELETE statement.
UTF8MB4 as the Default Character Set: MySQL 8.0 made UTF8MB4 the default character set, providing better support for storing and processing Unicode characters, including emojis.
Persistent Configuration Variables: Configuration changes made using SET PERSIST or SET PERSIST_ONLY are persistent across server restarts. This simplifies the management of server settings.
Invisible Indexes: MySQL 8.0 introduced the ability to mark an index as invisible. This allows database administrators to test the query performance impact of removing an index without actually dropping it.
Better JSON Support: MySQL 8.0 enhanced JSON support with new functions, operators, and indexing capabilities, making it easier to work with JSON data.
Improved Performance: MySQL 8.0 included various performance improvements, including better handling of read and write workloads, faster and more scalable replication, and enhanced support for high-performance computing environments.
Atomic DDL Statements: Data Definition Language (DDL) statements can now be performed atomically, ensuring that either the entire statement is executed, or none of it is. This helps in preventing partial execution of DDL statements.
Resource Group Management: MySQL 8.0 introduced the ability to manage system resources by creating and configuring resource groups. This allows users to allocate resources based on priority and importance.
Data Redaction: MySQL 8.0 added support for data redaction policies, enabling users to define rules for masking or redacting sensitive information in query results. This enhances data privacy and security.
Clone Plugin: The clone plugin allows for the efficient and quick cloning of InnoDB tables without requiring a full table copy. This feature is particularly useful for creating replicas or backups.
Instant ADD COLUMN: MySQL 8.0 introduced the capability to add a new column to a table with minimal disruption. This "instant ADD COLUMN" feature avoids the need for a full table rebuild, resulting in faster and more efficient schema changes.
Histograms: The introduction of histograms in MySQL 8.0 provides more accurate statistics for the query optimizer, improving the quality of query execution plans and overall query performance.
Default Values for JSON Columns: In MySQL 8.0, users can specify default values for JSON columns. This simplifies data input and ensures consistency in cases where certain fields are commonly set to predefined values.
Persistent AUTO_INCREMENT: The AUTO_INCREMENT attribute for columns can now be made persistent using the AUTO_INCREMENT option in the CREATE TABLE statement. This allows users to explicitly set the starting value for the auto-incremented column.
Group Replication Enhancements: MySQL 8.0 improved Group Replication, a feature that enables high availability and fault tolerance by replicating data to multiple servers. Enhancements include support for multi-primary mode and enhanced conflict resolution.
MySQL Document Store: MySQL 8.0 continued to enhance its support for the Document Store, a NoSQL JSON-based API. This allows developers to work with both SQL and NoSQL data models within the same database.
Multi-Value Indexes: MySQL 8.0 introduced support for multi-value indexes, allowing users to create indexes on the elements of arrays in JSON documents. This improves query performance for certain types of JSON queries.
Features Deprecated in MySQL 8.0
MySQL 8.0, like any major software release, introduced changes not only in terms of new features but also by deprecating certain functionalities. Deprecation means that while a feature is still available in the current version, it is advised against and may be removed in future releases. Here are some of the features deprecated in MySQL 8.0:
The mysql.proc Table: In MySQL 8.0, the mysql.proc table, which stored stored procedure and function information, is deprecated. Instead, this information is now stored in the new data dictionary tables.
--skip-grant-tables Option: The --skip-grant-tables option, which allows the server to start without using the privilege system, is deprecated. It's a security risk, and users are encouraged to use more secure methods for password recovery or reset.
SHOW ENGINE INNODB STATUS Output: The SHOW ENGINE INNODB STATUS output is deprecated in MySQL 8.0. Users are encouraged to use the Information Schema tables and the Performance Schema for monitoring InnoDB status.
SHOW PROFILE Statement: The SHOW PROFILE statement and associated profiling code are deprecated. Users are advised to use Performance Schema instruments and consumers for detailed performance analysis.
OLD_PASSWORD() Function: The OLD_PASSWORD() function, used for pre-4.1-style password hashing, is deprecated. Users should migrate to using the more secure authentication mechanisms introduced in MySQL 4.1.
--symbolic-links Option: The --symbolic-links option for mysqld is deprecated. It was previously used to control whether the server follows symbolic links to databases. The behavior without this option is the recommended behavior.
--require_secure_transport Option: The --require_secure_transport option is deprecated. It was used to enforce secure connections but has been removed in later MySQL versions. Users are encouraged to use other means, such as setting up SSL.
--lock-wait-timeout Option: The --lock-wait-timeout option is deprecated. Users are recommended to use the innodb_deadlock_detect_interval system variable instead.