Mysql：Changes in MySQL 5.6.4 (2011-12-20, Milestone 7)：微秒支持、Innodb全文索引支持

Changes in MySQL 5.6.4 (2011-12-20, Milestone 7)

Fractional Seconds Handling

• Incompatible Change: MySQL now permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

  CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

  The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

  The following items summarize the implications of this change. See also Fractional Seconds in Time Values.

  • For TIME, DATETIME, and TIMESTAMP columns, the encoding and storage requirements in new tables differ from such columns in tables created previously because these types now include a fractional seconds part. This can affect the output of statements that depend on the row format, such as CHECKSUM TABLE.

  • Due to these changes in encoding and storage requirements for MySQL's DATETIME and TIMESTAMP types, importing pre-MySQL 5.6.4 InnoDB tables using ALTER TABLE ... IMPORT TABLESPACE that contain DATETIME and TIMESTAMP types into MySQL 5.6.4 (or later) requires a workaround procedure which is described in the “Server Changes” section of Changes in MySQL 5.6 .

  • Syntax for temporal literals now produces temporal values: DATE 'str', TIME 'str', and TIMESTAMP 'str', and the ODBC-syntax equivalents. The resulting value includes a trailing fractional seconds part if specified. Previously, the temporal type keyword was ignored and these constructs produced the string value. See Standard SQL and ODBC Date and Time Literals

  • Functions that take temporal arguments accept values with fractional seconds. Return values from temporal functions include fractional seconds as appropriate.

  • Three INFORMATION_SCHEMA tables, COLUMNS, PARAMETERS, and ROUTINES, now have a DATETIME_PRECISION column. Its value is the fractional seconds precision for TIME, DATETIME, and TIMESTAMP columns, and NULL for other data types.

  • The C API accommodates fractional seconds as follows:

    • In the MYSQL_FIELD column metadata structure, the decimals member indicates the fractional seconds precision for TIME, DATETIME, and TIMESTAMP columns. Clients can determine whether a result set temporal column has a fractional seconds part by checking for a nonzero decimals value in the corresponding MYSQL_FIELD structure. Previously, the decimals member indicated the precision for numeric columns and was zero otherwise.

    • In the MYSQL_TIME structure used for the binary protocol, the second_part member indicates the microseconds part for TIME, DATETIME, and TIMESTAMP columns. Previously, the second_part member was unused.

  In some cases, previously accepted syntax may produce different results. The following items indicate where existing code may need to be changed to avoid problems:

  • Some expressions produce results that differ from previous results. Examples: The timestamp system variable returns a value that includes a microseconds fractional part rather than an integer value. Functions that return a result that includes the current time (such as CURTIME(), SYSDATE(), or UTC_TIMESTAMP()) interpret an argument as an fsp value and the return value includes a fractional seconds part of that many digits. Previously, these functions permitted an argument but ignored it.

  • TIME values are converted to DATETIME by adding the time to the current date. (This means that the date part of the result differs from the current date if the time value is outside the range from '00:00:00' to '23:59:59'.) Previously, conversion of TIME values to DATETIME was unreliable. See Conversion Between Date and Time Types.

  • TIMESTAMP(N) was permitted in old MySQL versions, but N was a display width rather than fractional seconds precision. Support for this behavior was removed in MySQL 5.5.3, so applications that are reasonably up to date should not be subject to this issue. Otherwise, code must be rewritten.

  Note

  There may be problems replicating from a master server that understands fractional seconds to an older slave that does not:

  • For CREATE TABLE statements containing columns that have an fsp value greater than 0, replication will fail due to parser errors.

  • Statements that use temporal data types with an fsp value of 0 will work for with statement-based logging but not row-based logging. In the latter case, the data types have binary formats and type codes on the master that differ from those on the slave.

  • Some expression results will differ on master and slave. For example, expressions that involve the timestamp system variable or functions that return the current time have different results, as described earlier.

  (Bug #8523, Bug #11745064)

InnoDB Notes

• MySQL now supports FULLTEXT indexes for InnoDB tables. The core syntax is very similar to the FULLTEXT capability from earlier releases, with the CREATE TABLE and CREATE INDEX statements, and MATCH() ... AGAINST() clause in the SELECT statement. The new @ operator allows proximity searches for terms that are near each other in the document. The detailed search processing is controlled by a new set of configuration options: innodb_ft_enable_stopword, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_cache_size, innodb_ft_min_token_size, and innodb_ft_max_token_size. You can monitor the workings of the InnoDB full-text search system by querying new INFORMATION_SCHEMA tables: innodb_ft_default_stopword, innodb_ft_index_table, innodb_ft_index_cache, innodb_ft_config, innodb_ft_deleted, and innodb_ft_being_deleted.

Functionality Added or Changed

• Performance; InnoDB: New optimizations apply to read-only InnoDB transactions. See Optimizing InnoDB Read-Only Transactions for details. The new optimizations make autocommit more applicable to InnoDB queries than before, as a way to signal that a transaction is read-only because it is a single-statement SELECT.

• Performance; InnoDB: You can now set the InnoDB page size for uncompressed tables to 8KB or 4KB, as an alternative to the default 16KB. This setting is controlled by the innodb_page_size configuration option. You specify the size when creating the MySQL instance. All InnoDB tablespaces within an instance share the same page size. Smaller page sizes can help to avoid redundant or inefficient I/O for certain combinations of workload and storage devices, particularly SSD devices with small block sizes.