MySQL 8 Features: New features of MySQL 8.x
The following features of MySQL have been added to MySQL 8.x
- Character set support
- Window Functions
- Roles in MySQL 8.x
- Invisible indexes support
- MySQL Data Dictionary
- JSON Enhancements
You may also like - Database Interview Questions
The default character set has changed from latin1 to utf8mb4.
MySQL 8.x supports window functions. It performs an aggregate-like operation on a set of query rows.
In short, Window Functions is like GROUP BY but it preserve rows rather than collapsing them.
|CUME_DIST()||Cumulative distribution value|
|ROW_NUMBER()||Number of current row within its partition|
|RANK()||Rank of current row within its partition, with gaps|
|DENSE_RANK()||Rank of current row within its partition, without gaps|
|PERCENT_RANK()||Percentage rank value|
|FIRST_VALUE()||Value of argument from first row of window frame|
|LAST_VALUE()||Value of argument from first row of window frame|
You may also like - MySQL Interview Questions
- MySQL 8.x supports Roles, the ability of defining a set of privileges as a named role and then granting that set to one or more users
- The main benefits are more clarity of privileges and ease of administration.
- Using roles we can assign the same set of privileges to several users, and eventually modify or revoke all privileges at once.
MySQL 8.x supports Descending Indexes - DESC. A descending index is an index that stores key values in the descending order.
MySQL now added a transactional data dictionary which stores information about database objects. In previous MySQL releases, dictionary data was stored in metadata files and nontransactional tables.
You may also like - MongoDB Interview Questions
MySQL 8.x added some new JSON functions and improves performance for sorting and grouping JSON values.
- ->> has been added, this is the equivalent of JSON_UNQUOTE(JSON_EXTRACT())
- New aggregation functions JSON_ARRAYAGG() and JSON_OBJECTAGG()
- Added some more function like - JSON_TABLE() and JSON_PRETTY()
- JSON utility function like JSON_STORAGE_SIZE() and JSON_STORAGE_FREE()
You may also like - Oracle DBA Interview Questions
- The utf8mb3 character set is deprecated in MySQL 8.x. Please use utf8mb4 instead.
- The PAD_CHAR_TO_FULL_LENGTH SQL mode is deprecated.
- The JSON_MERGE() function is deprecated. Use JSON_MERGE_PRESERVE() instead.
You may also like - React.js Interview Questions