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
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
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
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
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
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!