Blog / Development

Changes with MySQL 5.7

İlke Kalkan

İlke Kalkan
mysql-1024x358.jpg
Table of Contents

MySQL is a multi-threaded, multi-user, fast, and robust database management system installed on over six million systems.

It is distributed for free on UNIX, OS/2, and Windows platforms, but there is also a paid license option for those who want to use a commercial license. It performs faster under Linux. MySQL, which is open source, also has downloadable binary versions available for many platforms. Additionally, since it has ODBC drivers, it can be easily used on many development platforms.

It is the most used database on web servers and can be used with many web programming languages such as ASP and PHP.

In this article, we will examine the changes that came with version 5.7, the latest version of MySQL.

Added and Updated Features:

With the release of MySQL 5.7, the following features have been added.

Improved Security

  • Easier and more secure environment startup, installation, and management capabilities have been provided.
  • Database administrators' control capabilities have been enhanced.

InnoDB Improvements

  • Increased DDL performance and concurrency,
  • Native partitioning,
  • Spatial data types and indexes,
  • Enhanced online operations,
  • Improved Innochecksum functionality,
  • Developments related to caching.

Increased Speed

  • In comparison tests conducted with 1,024 connections using SysBench Read-only Point-Selects, MySQL 5.7 performed 1,600,000 queries per second (QPS), making it three times faster than MySQL 5.6.

New Native JSON Data Type and Functions

Developments related to JSON, which allows efficient and flexible definition, storage, searching, and modification of schema-less data, include easy integration with SQL and index management of JSON documents using created JSON columns.

Functions working with JSON values have also been included in MySQL 5.7.8. Some of these functions are;

  • Functions that create JSON values: JSON ARRAY(), JSON MERGE(), and JSON OBJECT()
  • Functions that search JSON values: JSON CONTAINS(), JSON CONTAINS PATH(), JSON EXTRACT(), JSON KEYS(), and JSON SEARCH()
  • Functions that modify JSON values: JSON APPEND(), JSON ARRAY APPEND(), JSON ARRAY INSERT(), JSON INSERT(), JSON QUOTE(), JSON REMOVE(), JSON REPLACE(), JSON SET(), and JSON UNQUOTE()
  • Functions that provide information about JSON: JSON DEPTH(), JSON LENGTH(), JSON TYPE(), and JSON VALID() MySQL SYS schema

Performance Schema

  • Helps database administrators or developers interpret data collected by the performance schema, including
  • Common performance,
  • Status,
  • Usage,
  • Observations.

Performance Schema

  • Provides access to information about system and status variables. This information includes memory, queries, stored routines, created tables, replication, and locks.

Optimizer Improvements

  • The dynamic cost model provides better performance in queries while giving users more control.

Trigger Improvements

  • In previous versions, a maximum of 1 use was allowed for each trigger event and time. This limitation has been removed in MySQL 5.7, and multiple triggers are now allowed.

Deprecated or No Longer Used Features

These features are deprecated in MySQL version 5.7 and will be removed in future updates.

SQL modes such as ERROR FOR DIVISION BY ZERO, NO ZERO DATE, NO ZERO IN DATE will be removed.

Changes have been made in account management statements:

The GRANT command will no longer be used to create new users or edit accounts. Instead, CREATE USER or ALTER USER commands will be used.

The PASSWORD() function and the SET PASSWORD . . . = PASSWORD ('auth string') syntax will no longer be used. The SET PASSWORD . . . 'auth string' usage is still valid, but using ALTER USER for setting passwords is recommended.

The –skip-innodb option (and its synonyms like –innodb=OFF, –disable-innodb) is ineffective in MySQL 5.7 because InnoDB cannot be disabled.

References:

İlke Kalkan

İlke Kalkan
“Writing is seeing the future.” Paul Valéry