List of differences between MySQL 8 and MySQL 7

Curious about the key differences between MySQL 8 and MySQL 7? MySQL 8 introduces a host of new features and enhancements that set it apart from its predecessor. Below is a comprehensive list of the most notable changes and improvements you’ll find in MySQL 8.

• The default storage engine is InnoDB, whereas in MySQL 7 it was MyISAM.
• The default character set and collation are utf8mb4 and utf8mb4_0900_ai_ci, respectively; in MySQL 7, they were latin1 and latin1_swedish_ci.
• The ON UPDATE CURRENT_TIMESTAMP clause can be used in TIMESTAMP column definitions to automatically update the column to the current timestamp when the row is modified.
• The GROUPING SETS clause allows you to specify multiple grouping sets in a single GROUP BY query.
• The ROW_NUMBER() window function can assign a unique integer value to each row in the result set.
• The DESCRIBE statement has been replaced by EXPLAIN, which provides more detailed information about a query’s execution plan.
• The ALTER USER statement now supports additional options for modifying user accounts, such as setting the default schema and authentication plugin—features not available in MySQL 7.
• The JSON_TABLE() function enables conversion of a JSON value to a table, which is not possible in MySQL 7.
• The JSON_EXTRACT() function now supports more options for extracting values from JSON documents, such as extracting values at specific paths or retrieving object keys.
• The SHOW CREATE statement has been enhanced to support more objects, including sequences, events, and user-defined functions.
• The SHOW WARNINGS statement now includes the statement that caused the warning, providing more context than in MySQL 7.
• The DEFAULT ROLE clause can be used in GRANT statements to specify a user’s default role.
• The HANDLER statement allows inspection of the state of a cursor or query result set, a feature not found in MySQL 7.
• The CHECKSUM TABLE statement can compute the checksum of one or more tables, which was not available in MySQL 7.
• The WITHOUT VALIDATION clause in ALTER TABLE statements lets you skip validation of foreign key constraints.
• The START TRANSACTION statement allows you to begin a transaction with a specified isolation level.
• The UNION [ALL] clause can be used in SELECT statements to combine results from multiple queries.
• The FULLTEXT INDEX clause in CREATE TABLE statements enables creation of full-text indexes on one or more columns.
• The ON DUPLICATE KEY UPDATE clause in INSERT statements specifies an update action when a duplicate key error occurs.
• The SECURITY DEFINER clause in CREATE PROCEDURE and CREATE FUNCTION statements allows execution with the privileges of the definer, not the invoker.
• The ROW_COUNT() function retrieves the number of rows affected by the last statement, which is not available in MySQL 7.
• The GRANT USAGE ON . statement can grant a user access to the server without granting access to specific databases or tables.
• The DATE_ADD() and DATE_SUB() functions now support additional date and time units, such as seconds, minutes, and hours.
• The EXPLAIN FORMAT=JSON clause in EXPLAIN statements returns the execution plan in JSON format.
• The TRUNCATE TABLE statement can truncate multiple tables in a single operation.
• The AS OF clause in SELECT statements lets you query the state of a table at a specific point in time.
• The WITH SYSTEM VERSIONING clause in CREATE TABLE statements enables system-versioned tables, which automatically track the history of changes to table data.
• The UNION [ALL] clause can also be used in DELETE and UPDATE statements to apply operations to multiple tables at once.
• The INSERT … ON DUPLICATE KEY UPDATE statement allows you to insert rows or update existing ones if new data conflicts with primary key or unique index values.
• The WITHOUT_DEFAULT_FUNCTIONS clause in DROP DATABASE statements prevents deletion of default functions such as now() and uuid().
• The JSON_EXTRACT_SCALAR() function can extract a scalar value from a JSON document, a feature not present in MySQL 7.