MySQL is arguably the most popular and common RDBMS (relational database management system). Its latest major release MySQL 8.0 was released to the public in April last year, and it brought with it some very exciting new enhancements.
So, without further ado, let’s cast look at some of the top enhancements.
Features Added in MySQL 8.0
Better Unicode Support
UTF-8 is the dominating character encoding for both web and modern apps (this means mobile). Although there are many factors behind its dominance but the main has been its support for emojis.
Great news for a majority of MySQL users, the latest version no longer uses latin1 (a very problematic legacy option) as the default encoding to discourage new users from opting for it. The recommended default character set for the latest version is now utf8mb4, which is faster than the utf8mb3 character set (now-deprecated) and supports more flexible collations and case sensitivity.
Window Functions
MySQL now supports window functions. An extremely useful feature, it performs a calculation like count across a set of rows that are related to the current row, similar to an aggregate function.
However, what differentiates it from an aggregate function is that it does not cause rows to collapse into a single output row. Thus enabling users to perform aggregate calculations across multiple rows while still having access to individual rows “in the vicinity” of the current row.
These include functions such as RANK(), CUME_DIST(), LAG(), LEAD() and NTILE(). In addition, several existing aggregate functions now can be used as window functions (for instance, SUM() and AVG()).
Improved JSON Support
In order to keep-up with NoSQL databases that use JSON natively, last MySQL major release introduced JSON support which included virtual columns, the introduction of a JSON data type, and a set of twenty SQL functions that enables users to manipulate and search JSON data on the server side. Continuing the tradition, the latest version brings new improvements in the collection of JSON functions:
- ->> has been added, this is the equivalent of JSON_UNQUOTE(JSON_EXTRACT())
- new aggregation functions JSON_ARRAYAGG() and JSON_OBJECTAGG()
- addition of JSON_PRETTY()
- new JSON utility functions like JSON_STORAGE_SIZE(), JSON_STORAGE_FREE()
Configuration Persistence
The latest version prefers the SET PERSIST instead of SET GLOBAL for changing configuration during MySQL runtime. The difference between the two is that if the latter is used to make changes they will not survive a server restart, while the changes done via the former can survive a MySQL server restart.
SET PERSIST works with any configuration variables, including offline_mode, read_only, and so forth. What makes it even better is that it does not require file system access, so don’t worry if you don’t have system file access.
New Database Roles
First, what is a role? Well, its a named group of privileges that specify what a user can and cannot do within a database. They play an important part in the security of database by limiting who can connect to the server, access the database, and so forth.
Although, roles did exist prior to 8.0, this latest version also supports a set of flexible and properly architected roles (which previous versions lacked), allowing database administrators (DBAs) to:
- Create and drop roles, grant to roles
- Grant roles to roles, grant roles to users
- Limit hosts that can use roles, define default roles
- And the list goes on.
Since each role packs multiple privileges, database administrators don’t have to remember exactly which permissions a user requires. Roles are also very easy to set up.
Invisible Indexes Support
In simple words, with invisible indexes you can hide an index you currently don’t need, without actually dropping it. All you have to do is to mark that index as invisible and MySQL optimizer won’t use it. One thing that need to be mentioned here is that unlike disabled indexes, the index information here remains fully up to date and maintained by DML; it’s just invisible to the MySQL Optimizer. After monitoring your server and queries performance, you can re-activate it if you believe it will result in a performance boost.
Common Table Expressions (CTEs)
Although new to MySQL, CTE (also known as WITH queries) is already available in other databases, it basically simplifies the way you write complex queries. It is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, recursively or not. This feature introduces both readability and performance improvements.
From new database roles and configuration persistence to recursive CTEs and window functions, we have been through some of the top features added to the latest version. Now, let’s take a look at some of the depreciated features.
Features Deprecated in MySQL 8.0
- As mentioned above, the utf8mb3 character set is deprecated. It has been replaced by utf8mb4.
- Accounts that authenticate using sha256_password should be migrated to use caching_sha2_password instead. As the former have been deprecated because the latter provides a superset of the capabilities of the sha256_password authentication plug-in.
- The ALTER TABLESPACE and DROP TABLESPACE ENGINE clause is deprecated.
- AUTO_INCREMENT support is deprecated for columns of type FLOAT and DOUBLE (and any synonyms). Consider removing the AUTO_INCREMENT attribute from such columns, or convert them to an integer type.
- The PAD_CHAR_TO_FULL_LENGTH SQL mode is deprecated.
- The UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms). Consider using a simple CHECK constraint instead for such columns.
- The ZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types. Consider using an alternative means of producing the effect of these attributes. For example, applications could use the LPAD() function to zero-pad numbers up to the desired width, or they could store the formatted numbers in CHAR columns.
- The –no-dd-upgrade server option is deprecated and replaced by the –upgrade option, which provides finer control over data dictionary and server upgrade behavior.
- And the list goes on.