Password Locking
MySQL now enables administrators to configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements.
YEAR(4) and Year
Support for the YEAR(2) data type was removed in MySQL 5.7.5, leaving only YEAR and YEAR(4) as valid specifications for year-valued data. Because YEAR and YEAR(4) are semantically identical, specifying a display width is unnecessary, so YEAR(4) is now deprecated and support for it will be removed in a future MySQL version. Statements that include data type definitions in their output no longer show the display width for YEAR. This change applies to tables, views, and stored routines, and affects the output from SHOW CREATE and DESCRIBE statements, and from INFORMATION_SCHEMA tables.
Syntax Change
MySQL now supports explicit table clauses and table value constructors according to the SQL standard. These have now been implemented, respectively, as the TABLE statement and the VALUES statement. What does that mean?
You can use TABLE like you use SELECT. The two following statements are equivalent;
TABLE t ORDER BY c LIMIT 10 OFFSET 3; SELECT * FROM t ORDER BY c LIMIT 10 OFFSET 3;
And ROW will help ease confusion for those adding multiple records in a statement. Again the following two statements are equivalent.
INSERT INTO t1 VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9); INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9);
You can also select from a VALUES table value constructor just as you would a table, bearing in mind that you must supply a table alias when doing so. Using column aliases, you can also select individual columns
mysql> SELECT a,c FROM (VALUES ROW(1,2,3), ROW(4,5,6)) AS t(a,b,c); +---+---+ | a | c | +---+---+ | 1 | 3 | | 4 | 6 | +---+---+
Recursion Limits on CTEs
Before 8.0.19 it was not possible to use LIMIT in the recursive SELECT part of a recursive common table expression (CTE). LIMIT is now supported in such cases, along with an optional OFFSET clause
WITH RECURSIVE cte AS ( SELECT CAST("x" AS CHAR(100)) AS a FROM DUAL UNION ALL SELECT CONCAT("x",cte.a) FROM qn WHERE LENGTH(cte.a) < 10 LIMIT 3 OFFSET 2; ) SELECT * FROM cte;
Alias on DUPLICATE KEY Updates
MySQL now supports aliases in the VALUES and SET clauses of INSERT INTO ... ON DUPLICATE KEY UPDATE statement for the row to be inserted and its columns. Note the use of 'new' as an alias.
INSERT INTO t VALUES (9,5), (7,7), (11,-1) AS new ON DUPLICATE KEY UPDATE a = a + new.a - new.b;
Microsoft Windows System Command
Previously, the system (\!) command for the mysql command-line client worked only for Unix systems. It now works on Windows as well. For example, system cls or \! cls may be used to clear the screen
X Protocol Compression
Compression is supported for messages sent over X Protocol connections. Connections can be compressed if the server and the client agree on a compression algorithm to use. By default, X Protocol announces support for the deflate, lz4, and zstd compression algorithms.
Row Based Replication for Security
A new setting REQUIRE_ROW_FORMAT is added for replication channels, which makes the channel accept only row-based replication events. You can specify REQUIRE_ROW_FORMAT using a CHANGE MASTER TO statement to enforce row-based binary logging for a replication channel that is secured with privilege checks, or to increase the security of a channel that is not secured in this way. By allowing only row-based replication events, REQUIRE_ROW_FORMAT prevents the replication applier from taking actions such as creating temporary tables and executing LOAD DATA INFILE requests, which protects the replication channel against some known attack vectors. Row-based binary logging (binlog_format=ROW) must be used on the replication master when REQUIRE_ROW_FORMAT is set.
But Wait -- There's More
These were only the big items that caught my eye and a recommend reading the release notes to see if there is something that did not catch my eye that may be of interest for you.
