What's New In MySQL 5.7
The official documentation about what has changed in 5.7, but I'll summarize the most exciting changes below:
Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, but are stored using an internal binary format that permits quick read access to document elements. JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error. There are a number of operations and functions made available specifically for this new type that will be made available in a future post, but for now you can read the official documantation.
Generated Columns (Virtual Columns)
MySQL now supports the specification of generated columns in
CREATE TABLE and
ALTER TABLE statements. Values of a generated column are computed from an expression specified at column creation time. Generated columns can be virtual (computed “on the fly” when rows are read) or stored (computed when rows are inserted or updated). For more information, see CREATE TABLE and Generated Columns. I also have an old post on virtual columns with MariaDB, which it has had for some time now.
Strict By Default
Strict SQL mode for transactional storage engines (
STRICT_TRANS_TABLES) is now enabled by default. This is a very good thing and is discussed in my previous post about MySQL 5.6 "MySQL - Prevent Defaulting to Allow Defaults".
InnoDB now supports MySQL-supported spatial data types. Prior to this release, InnoDB would store spatial data as binary BLOB data. BLOB remains the underlying data type but spatial data types are now mapped to a new InnoDB internal data type, DATA_GEOMETRY.
As of MySQL 5.7.5, InnoDB supports indexing of spatial data types using SPATIAL indexes.
As of MySQL 5.7.6, InnoDB supports native partitioning. Previously, InnoDB relied on the ha_partition handler, which creates a handler object for each partition. With native partitioning, a partitioned InnoDB table uses a single partition-aware handler object. This enhancement reduces the amount of memory required for partitioned InnoDB tables.
First published: 16th August 2018