SQL syntax
Table value constructors are another improvement towards the standard allowing generation of rows with the
VALUES statement (not to be confused with
VALUES keyword for
INSERT statements). Due to name conflict with the
VALUES function it's necessary to use the verbose form with
ROW constructor. This basically means the standalone statement
VALUES ROW(1), ROW(2), ROW(3) is now properly understood by the server.
VALUES statement could be used also in unions, joins, as derived table and other cases where you could employ
SELECT. See the
VALUES statement article in documentation for more details.
The new
TABLE statement implements another standard feature - explicit table clauses. It could be used like
SELECT, because
TABLE t is just like
SELECT * FROM t, so it could be used in unions, with
INTO, as
IN subquery and elsewhere where
SELECT could be employed.
It's is now possible to use an alias for the new row and optionally columns with
INSERT ... ON DUPLICATE KEY UPDATE statement following the
VALUES or
SET clause with the use of
AS keyword. I guess this is the first step to removing
VALUES function (see above) for better standards support. You could find more in the article for the
ON DUPLICATE KEY UPDATE Statement.
Datatype changes
The TIMESTAMP and DATETIME types could now include timezone information as offset (e.g. +02:00 or -08:00) appended to the value. The details are in the article
The DATE, DATETIME, and TIMESTAMP Types in documentation.
The
YEAR(4) data type (i.e. with explicit display width) and the (undocumented)
UNSIGNED attribute for
YEAR are now deprecated and would be removed in future. This follows other deprecations of display widths and other type attributes from 8.0.17 for which I
wrote before, so developers should check and correct any SQL scripts using deprecated types and attributes. With this release statements that print data type definitions (e.g.
SHOW CREATE TABLE) no longer show display width for integers with exception for
TINYINT(1) and
ZEROFILL attribute.
Other changes in server
InnoDB engine now supports efficient sampling of data for
histogram statistics. Sampling no longer requires full table scan, which could be costly for large tables, but samples data pages from the clustered index instead. I should try this on first opportunity.
Bugs fixed in server
The previously reported problem with
EXPLAIN ANALYZE not executing and reporting on subqueries in the select list has been fixed (see bug
97296).
Workbench
As I read in the
release notes of the new version that "
The following MySQL server language features are now supported: value references in INSERT statements, new options (TLS version, cipher suite, compression, and privilege check) in CHANGE MASTER TO replication statements, random passwords in CREATE USER and ALTER USER statements, EXPLAIN ANALYZE, and binary collation names.", I was hopeful that the application is finally catching up with the server, but that was until I reviewed the new SQL syntax (see above).
Two of the bugs I reported previously were fixed -
97416 (for
EXPLAIN ANALYZE) and
97281 (for
ANALYZE TABLE ... UPDATE HISTOGRAM), but I opened two new for
TABLE (see bug
98263) and
VALUES (see bug
98266) statements. Such problems should be obvious, so it still strange to me how they continue appearing.
There is a new menu item "Copy Row (with names, tab separated)" for copying headers and rows of queried data with tabs as separators that makes it easy to move results into Excel or LibreOffice Calc.
Happy using and experimenting with the new MySQL releases!
