List of differences between MySQL 8 and MySQL 7

MySQL 8 has some of the most notable differences including:

• the default storage engine is InnoDB, whereas in MySQL 7 it is MyISAM.
• the default character set and collation are utf8mb4 and utf8mb4_0900_ai_ci, respectively, whereas in MySQL 7 they are latin1 and latin1_swedish_ci, respectively.
• 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 updated.
• the GROUPING SETS clause can be used in GROUP BY statements to specify multiple grouping sets in a single query.
• the ROW_NUMBER() window function can be used to assign a unique integer value to each row in the result set.
• the DESCRIBE statement has been replaced by the EXPLAIN statement, which provides more detailed information about the execution plan of a query.
• the ALTER USER statement has been extended to support more options for modifying user accounts, such as setting the default schema and authentication plugin, whereas in MySQL 7 these options are not available.
• the JSON_TABLE() function can be used to convert a JSON value to a table, whereas in MySQL 7 this function is not available.
• the JSON_EXTRACT() function has been extended to support more options for extracting values from JSON documents, such as extracting a value at a specific path or extracting the keys of an object, whereas in MySQL 7 these options are not available.
• the SHOW CREATE statement has been extended to support more objects, such as sequences, events, and user-defined functions, whereas in MySQL 7 these objects are not supported.
• the SHOW WARNINGS statement has been extended to include the statement that caused the warning, whereas in MySQL 7 this information is not available.
• the DEFAULT ROLE clause can be used in GRANT statements to specify the default role for a user.
• the HANDLER statement can be used to inspect the state of a cursor or the result set of a query, whereas in MySQL 7 this statement is not available.
• the CHECKSUM TABLE statement can be used to compute the checksum of a table or a set of tables, whereas in MySQL 7 this statement is not available.
• the WITHOUT VALIDATION clause can be used in ALTER TABLE statements to skip the validation of foreign key constraints.
• the START TRANSACTION statement can be used to begin a transaction with the specified isolation level.
• the UNION [ALL] clause can be used in SELECT statements to combine the results of multiple queries.
• the FULLTEXT INDEX clause can be used in CREATE TABLE statements to create a full-text index on one or more columns.
• the ON DUPLICATE KEY UPDATE clause can be used in INSERT statements to specify an update action to be performed when a duplicate key error occurs.
• the SECURITY DEFINER clause can be used in CREATE PROCEDURE and CREATE FUNCTION statements to specify that the procedure or function should be executed with the privileges of the definer, rather than the invoker.
• the ROW_COUNT() function can be used to retrieve the number of rows affected by the last statement, whereas in MySQL 7 this function is not available.
• the GRANT USAGE ON . statement can be used to grant a user access to the server without granting access to any specific databases or tables.
• the DATE_ADD() and DATE_SUB() functions have been extended to support more date and time units, such as seconds, minutes, and hours, whereas in MySQL 7 these units are not supported.
• the EXPLAIN FORMAT=JSON clause can be used in EXPLAIN statements to return the execution plan of a query in JSON format.
• the TRUNCATE TABLE statement can be used to truncate multiple tables in a single operation.
• the AS OF clause can be used in SELECT statements to query the state of a table at a specific point in time.
• the WITH SYSTEM VERSIONING clause can be used in CREATE TABLE statements to enable system-versioned tables, which automatically track the history of changes to the table data.
• the UNION [ALL] clause can be used in DELETE and UPDATE statements to apply the operation to multiple tables in a single operation.
• the INSERT … ON DUPLICATE KEY UPDATE statement can be used to insert rows into a table, or update existing rows if the new data conflicts with the existing primary key or unique index values.
• the WITHOUT_DEFAULT_FUNCTIONS clause can be used in DROP DATABASE statements to prevent the deletion of default functions, such as now() and uuid().
• the JSON_EXTRACT_SCALAR() function can be used to extract a scalar value from a JSON document, whereas in MySQL 7 this function is not available.