This post will be updated as soon more information comes along.
This developer version wasn’t released yet, when it does, use at your own risk.
Oracle released the development version of MySQL 8.0.0-dmr on September 12th of 2016. Since then, the team have been working on the 8.0.1 development milestone. You can find the partial change list here.
The objective here is try to explain how this will have any real world impact for you from 8.0.1. Please reember though, that any changes made to this version will not be final until the General Availability (date not currently set).
These topics are aimed at the Software Engineering side and not DBA and this is why Replication, for instance, is not covered here.
Charset and Collation
MySQL 8.0 was defined as to have
utf8mb4 as the default
CHARACTER SET and
utf8mb4_general_ci as the default
COLLATION. 8.0.1 will change the default
Let’s analyse the name
0900refers to Unicode Collation Algorithm version.
airefers to accent insensitive.
cirefers to case insensitive.
How does that impact you? It means that by default, new tables will have that collation and will be able to handle more characters than Basic Multilingual Plane (more emoji! 🤦🏼♀️), plus it will be accent and case insensitive. If you want case and accent sensitive you will need to use
If you wish to know more about the reasons for
utf8 now being
utf8mb4 you should read this post on MySQL official blog: Sushi = Beer ?! An introduction of UTF8 support in MySQL 8.0
Language Specific Charsets
There are cases where language takes precedence over the default general collation. For this you will need to use, for example in the instance of German phone book order,
This particularly is one of my most desired features. Finally being implemented on this version, the
ALTER TABLE ADD INDEX ix_column (column DESC) won’t be parsed as
Since InnoDB uses
BTREE indexes, when running a query that uses it in the case of single columns it doesn’t matter if the index is
DESC. The index is used for
However when working with multi-column indexes this will matter and having a descending index will actually increase your performance. Example:
A generic table
|updated_at||timestamp||NO||CURRENT_TIMESTAMP||on update CURRENT_TIMESTAMP|
The following query will have better performance if run with
DESC index on
updated_at time column:
SELECT * FROM `users` ORDER BY updated_at DESC, first_name ASC LIMIT 100;
For that we need the following index:
ALTER TABLE `users` ADD INDEX `ix_updated_at_first_name` (updated_at DESC, first_name ASC);
Before that, the query plan would do a full scan on the table and not use the index at all. After the index is created it searches on index, and not the table:
The query above is just a simplistic example and doesn’t filter anywhere the index, this is why it causes to do a full index scan.
I personally never made use for
\N to indicate
NULL in any query itself, but I have used it in CSV or TSV files.
This behaviour won’t be supported anymore.
This change won’t impact file import or export through
LOAD DATA INFILE or
SELECT ... INTO OUTFILE.
In MySQL 5.7 the Generated Columns feature was added. However
ALTER TABLE in tables containing one would be a
COPY operation (which is slower since it has to copy all data again of the table). Now it can be
INPLACE as long as the column(s) being modified is not in a generated column. What this actually means is: the metadata for the column will be changed in real time, without the need to internally create a new table and copy data.
Two new functions are added to aggregate
JSON_ARRAYAGG() takes a column or expression as an argument and aggregates the result in a single JSON array.
JSON_OBJECTAGG(id, col) you can use two columns which will be interpreted as key and value and returns a single JSON object.
JSON columns when used with
ORDER BY was improved. Before MySQL would allocate 1K of memory to a sort key, making it fixed length. The extra padding was removed in this version.
The indexing of JSON fields is only possible through Generated Columns. If you tried to index a JSON field before, would get the following as an error:
JSON column '%s' cannot be used in key specification.
The error message has now been made clearer:
JSON column '%s' supports indexing only via generated columns on a specified JSON path.
More than 100 bugs were fixed on this version. It ranges from InnoDB through Replication and even compilation bugs. The list is too big to be tacked on to this article, but the complete (and yet partial) change list is available here.