Mysql：Changes in MySQL 8.0.19 (2020-01-13, General Availability):（\!）可以在windows下正常工作了！

Changes in MySQL 8.0.19 (2020-01-13, General Availability)

Deprecation and Removal Notes

• Setting the hash_join optimizer switch (see optimizer_switch system variable) no longer has any effect. The same applies with respect to the HASH_JOIN and NO_HASH_JOIN optimizer hints. Both the optimizer switch and the optimizer hint are now deprecated, and subject to removal in a future release of MySQL.

  This also fixes an issue whereby SELECT DISTINCT ... WITH ROLLUP did not always return all distinct rows. (Bug #27549694, Bug #30471809)

• 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.

  For DESCRIBE statements and INFORMATION_SCHEMA queries, output is unaffected for objects created in previous MySQL 8.0 versions because information already stored in the data dictionary remains unchanged. This exception does not apply for upgrades from MySQL 5.7 to 8.0, for which all data dictionary information is re-created such that data type definitions do not include display width.

  The (undocumented) UNSIGNED attribute for YEAR is also now deprecated and support for it will be removed in a future MySQL version.

SQL Syntax Notes

• Important 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, each described in brief here:

  • TABLE table_name is equivalent to SELECT * FROM table_name, and can be used anywhere that the equivalent SELECT statement would be accepted; this includes joins, unions, INSERT ... SELECT statements, REPLACE statements, CREATE TABLE ... SELECT statements, and subqueries.

    You can use ORDER BY with TABLE, which also supports LIMIT with optional OFFSET; these clauses function in the same way in a TABLE statement as they do with SELECT. The following two statements produce the same result:

    TABLE t ORDER BY c LIMIT 10 OFFSET 3;
    
    SELECT * FROM t ORDER BY c LIMIT 10 OFFSET 3;

  • VALUES consists of the VALUES keyword followed by a series of row constructors (ROW()), separated by commas. It can be used to supply row values in an SQL-compliant fashion to an INSERT statement or REPLACE statement. For example, 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, like this:

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

    You can employ such SELECT statements in joins, unions, subqueries, and other constructs in which you normally expect to be able to use such statements.

  For more information and examples, see TABLE Statement, and VALUES Statement, as well as INSERT ... SELECT Statement, CREATE TABLE ... SELECT Statement, JOIN Clause, UNION Clause, and Subqueries. (Bug #77639)

• Previously, 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. An example of such a recursive CTE is shown here:

  WITH RECURSIVE cte AS  (
    SELECT CAST("x" AS CHAR(100)) AS a FROM DUAL
    UNION ALL
    SELECT CONCAT("x",cte.a) FROM cte
      WHERE LENGTH(cte.a) < 10
      LIMIT 3 OFFSET 2
  )
  SELECT * FROM cte;

  This statement produces the following output in the mysql client:

  +-------+
  | a     |
  +-------+
  | xxx   |
  | xxxx  |
  | xxxxx |
  +-------+

  Specifying LIMIT in this fashion can make execution of the CTE more efficient than doing so in the outermost SELECT, since only the requested number of rows is generated.

  For more information, see Recursive Common Table Expressions. (Bug #92857, Bug #28816906)

Functionality Added or Changed

• Microsoft Windows: 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. (Bug #11765690, Bug #58680)

• MySQL now supports datetime literals with time zone offsets, such as '2019-12-11 10:40:30-05:00', '2003-04-14 03:30:00+10:00', and '2020-01-01 15:35:45+05:30'; these offsets are respected but not stored when inserting such values into TIMESTAMP and DATETIME columns; that is, offsets are not displayed when retrieving the values.

  The supported range for a timezone offset is -14:00 to +14:00, inclusive. Time zone names such as 'CET' or 'America/Argentina/Buenos_Aires', including the special value 'SYSTEM', are not supported in datetime literals. In addition, in this context, a leading zero is required for an hour value less than 10, and MySQL rejects the offset '-00:00' as invalid.

  Datetime literals with time zone offsets can also be used as parameter values in prepared statements.

  As part of this work, the allowed range of numeric values for the time_zone system variable has been changed, so that it is now also -14:00 to +14:00, inclusive.

  For additional information and examples, see The DATE, DATETIME, and TIMESTAMP Types, and MySQL Server Time Zone Support. (Bug #83852, Bug #25108148)