http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html
MySQL57新特性 Added Features 安全提升SQL mode changesOnline ALTER TABLEngram and MeCab full-text parser pluginsInnoDB enhancements - 在线修改varchar长度支持in-place算法 JSON supportSystem and status variablessys schemaCondition handlingOptimizerTriggersLoggingGenerated Columnsmysql clientDatabase name rewriting with mysqlbinlogHANDLER with partitioned tablesIndex condition pushdown support for partitioned tablesWITHOUT VALIDATION support for ALTER TABLE EXCHANGE PARTITIONMaster dump thread improvementsGlobalization improvementsChanging the replication master without STOP SLAVETest suiteMulti-source replication is now possibleGroup Replication Performance Schema tablesGroup Replication SQL Deprecated Features sql_mode关于帐户管理有些语句淘汰了所以相关内容也是废弃了group by 排序在执行计划explain语句不要再使用extended和partitions仍然会识别但是已经没有用了skip-innodbinnodbOFF disable-innodb 弃用了因为57innodb是不能禁用的客户端用SSL命令的变化log_warnings变量和--log-warnings选项弃用了用log_error_verbosity变量代替参数 binlog_max_flush_queue_time弃用参数innodb_support_xa 弃用metadata_locks_cache_size和metadata_locks_hash_instances废弃在57后什么也不做sync_frm 废弃全局变量 character_set_database and collation_database废弃ENCRYPT ENCODE DECODE DES_ENCRYPT and DES_DECRYPT 这些全部废弃建议用AES_ENCRYPT and AES_DECRYPT地理空间函数 MBREqual废弃用MBREquals代替INFORMATION_SCHEMA PROFILING 表废弃用 Performance Schema代替INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS废弃mysqld_safe support for syslog output is deprecated Use the native server syslog support used instead51之前的版本包含特殊字符该特性已经废弃因此mysqlcheck命令的 --fix-db-names and --fix-table-names 废弃ALTER DATABASE 语句 的 UPGRADE DATA DIRECTORY NAME从句废弃了而要从51之前的版本升级到新版本首先要先升级到51如50要升级为55而先从50升到51再从51升级到55mysql_install_db已经集成到了mysqld初始化数据用mysqld使用 --initialize or --initialize-insecure 选项并且会为rootlocalhost用户生成一个随机密码mysql_install_db废弃mysql_plugin工具废弃mysql_kill mysql_list_fields mysql_list_processes and mysql_refresh 已经废弃 Removed Features 由于41版本之前hash格式密码已经被移除有以下相关的变更字段类型 YEAR2 移除建议year4或year系统变量innodb_mirrored_log_groups只支持1所以没有用系统变量storage_engine 用 default_storage_engine代替系统变量thread_concurrency 移除系统变量timed_mutexes 没有任何影响ALTER TABLE的ignore从句INSERT DELAYED 不再支持服务器能认但是会忽略delayed关键字这种insert会当成 nondelayed insertwindows平台下的数据库超链接sym文件 移除了对于mysql_upgrade命令选项basedir datadir and tmpdir不再使用命令选项以后只支持全写不支持只写前辍命令SHOW ENGINE INNODB MUTEX 在572已经移除相关的信息在Performance Schema 中InnoDB Tablespace Monitor and InnoDB Table Monitor 在574已经移除相关信息在INFORMATION_SCHEMA中对于innodb和innodb的锁监控的是否开启特定表innodb_monitor and innodb_lock_monitor已经移除代替方案是系统变量innodb_status_output and innodb_status_output_locks系统变量 innodb_use_sys_malloc and innodb_additional_mem_pool_size 已经 移除The msql2mysql mysql_convert_table_format mysql_find_rows mysql_fix_extensions mysql_setpermission mysql_waitpid mysql_zap mysqlaccess and mysqlbug utilitiesmysqlhotcopy 工具The binary-configuresh scriptThe INNODB_PAGE_ATOMIC_REF_COUNT CMake option is removed in MySQL 575The innodb_create_intrinsic option is removed in MySQL 576The innodb_optimize_point_storage option and related internal data types DATA_POINT and DATA_VAR_POINT were removedThe innodb_log_checksum_algorithm option is removed in MySQL 579帐户可以加锁解锁，支持create user 和 alter user， 命令： lock_option: { ACCOUNT LOCK | ACCOUNT UNLOCK }
使用mysql_ssl_rsa_setup工具能够更容易使用ssl.
http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-rsa-files-using-mysql.html
初始化数据库时用mysqld –initialize替换以前的 mysql_install_db
ONLINE DDL支持rename index
ALTER TABLE now supports a RENAME INDEX clause that renames an index
alter table t1 rename index index_b to idx_colb;
全文索引支持中文，日语，韩文了。
As of MySQL 5.7.6, MySQL provides a built-in full-text ngram parser plugin that supports Chinese, Japanese, and Korean (CJK), and an installable MeCab full-text parser plugin for Japanese.
但这是条件的，只支持2种情况，一种是长度在0到-255之间增加，另一种是在256到更大之间。不支持由255之前的一个长度跳到256之后的长度。也不支持减长度。 不支持的情况用的是copy算法。 所以。在定varchar长度的时候，如果长度是大于200了，但是不确定以后会不会改的情况下，就直接写个256。
VARCHAR size may be increased using an in-place ALTER TABLE, as in this example: ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255); 临时表TEMPORARY table优化了DDL操作的性能。DDL performance for InnoDB temporary tables is improved through optimization of CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE statements.
临时表的元数据不再存innodb的系统表，单独存在INNODB_TEMP_TABLE_INFO指定的表里。包含所有用户的临时表信息。这个表在第一次执行select查询时创建。MySQL支持地理空间数据类型， 在此之间，mysql存在blob类型中。 地理空间数据，现在映射成内部的数据类型DATA_GEOMETRY
InnoDB now supports MySQL-supported spatial data types. Prior to this release, InnoDB would store spatial data as binary BLOB data. BLOB remains the underlying data type but spatial data types are now mapped to a new InnoDB internal data type, DATA_GEOMETRY.
Use the CREATE TABLE statement to create a table with a spatial column:
CREATE TABLE geom (g GEOMETRY); Use the ALTER TABLE statement to add or drop a spatial column to or from an existing table:
ALTER TABLE geom ADD pt POINT; ALTER TABLE geom DROP pt;
未压缩的临时表，存在独立的表空间里 。这个表空间每次重启都会重新创建，位置：innodb_temp_data_file_path
innochecksum（innodb离线文件校验工具）增强，以前只支持2G以内的文件，现在支持大于2G的文件 。
临时表不管压缩没压缩，都不会写redo，”non-redo”.因为临时表在数据库crash后不需要恢复。也就不需要redo log。但是为了支持事物是需要undo的，比如回滚操作，undo信息存在innodb_temp_data_file_path文件里（ibtmp1）。
innodb buffer pool的dump和load操作加强了，通过一个新的变量innodb_buffer_pool_dump_pct可以按百分比将最近用的page导出。这个操作是一个IO密集型的操作，可以通过 innodb_io_capacity 限制每秒buffer pool load操作的数量。（这个参数只会限制InnoDB background tasks，如flushing pages from buffer pool,从change buffer 里merging data,默认200）从5.7.3开始，支持全文解析插件。从buffer pool刷脏页时，5.7.4开始支持多个Page cleaner threads.一个新的参数用于指定数量：innodb_page_cleaners，默认值在5.7.8之前是1，在这个版本及之后是4.从5.7.4 online DDL (ALGORITHM=INPLACE) 从5.7.4开始支持rebuilding innodb表，不管是否分区。相关语句：
OPTIMIZE TABLE
ALTER TABLE … FORCE
ALTER TABLE … ENGINE=INNODB (when run on an InnoDB table)
As of 5.7.4, OPTIMIZE TABLE uses online DDL (ALGORITHM=INPLACE) for both regular and partitioned InnoDB tables. The table rebuild, triggered by OPTIMIZE TABLE and performed under the cover by ALTER TABLE … FORCE, is now performed using online DDL (ALGORITHM=INPLACE) and only locks the table for a brief interval, which reduces downtime for concurrent DML operations.
linux的Fusion-io Non-Volatile Memory (NVM)文件 系统提供原子写的能力，这和innodb的doublewrite buffer冗余了，在mysql5.7.4以后，在支持原子写的Fusion-io的设备上，doublewrite自动关闭了。
mysql5.7.4 传输表空间支持分区表。
mysql5.7.5支持参数innodb_buffer_pool_size在线修改。在重设的过程，相关的内存会移到新的内存区域，这个以chunk为单位 ，chunk的大小于innodb_buffer_pool_chunk_size决定，可以通过状态参数innodb_buffer_pool_resize_status 监控resizing的过程。在数据库shutdown和recovery阶段支持多线程的page cleaner，相关参数innodb_page_cleaners.在mysql5.7.5,innodb支持地理空间数据类型的索引spatial index,并且支持online ddl操作。在创建或重建索引时，innodb支持bulk load,这种方法叫”sorted index build”,这将提高创建索引的效率，同时可以应用在full-text 索引上，通过全局系统参数innodb_fill_factor,可以配置在每次sorted index build时每个page上使用百分比空间量（该参数默认100）。留一部分空间用于以后索引的增长。一个新的日志记录格式（MLOG_FILE_NAME）用于记录自动上次checkpoint后表空间的修改。这将简化crash recovery后表空间的发现，并且减少redo应用之前文件系统的扫描量。 这个特性将改变redo log的格式 ，升级或降级mysql5.7.5时需要完全关闭数据。mysql5.7.5，能在undo表空间truncate undo logs，这个特性的启用是能参数innodb_undo_log_truncate.从5.7.6，innodb支持native partitioning，在此之前，innodb依赖ha_partition handler，这将对每一个分区创建一个handler。 当用了native parttioning，分区的innodb表将只用一个parttion-aware handler对象。这个增强特性减少了parttioned innodb tables的内存需求量。
从5.7.6，innodb支持创建 general tablesapce 。语法如下：
CREATE TABLESPACE tablespace_name ADD DATAFILE ‘file_name.ibd’ [FILE_BLOCK_SIZE = n]
genaral tablespace 支持创建在mysql的data 目录之外，能保存多个表，并且对表支持所有row格式 。 表增加一个general tablespace 可以用以下语法：
CREATE TABLE tbl_name … TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name
http://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html
从5.7.9,innodb 表默认的行格式（row format）由compact替换为dynamic。参数innodb_default_row_format里多了一个新的配置选项。从5.7.11 ，innodb支持对file-per-table的表空间支持data-at-rest加密。当creating或者altering一个innodb表时，由 ENCRYPTION选项来启用加密。这个特性,涉及到innodb tablespace的加密，依赖于keying plugin做加密管理。json的操作需要相关函数来完成：
Functions that create JSON values: JSON_ARRAY(), JSON_MERGE(), and JSON_OBJECT(). See Section 13.16.2, “Functions That Create JSON Values”. Functions that search JSON values: JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_EXTRACT(), JSON_KEYS(), and JSON_SEARCH(). See Section 13.16.3, “Functions That Search JSON Values”. Functions that modify JSON values: JSON_APPEND(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_QUOTE(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET(), and JSON_UNQUOTE(). See Section 13.16.4, “Functions That Modify JSON Values”. Functions that provide information about JSON values: JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE(), and JSON_VALID(). See Section 13.16.5, “Functions That Return JSON Value Attributes”. x plugin(服务器端)：INSTALL PLUGIN mysqlx SONAME ‘mysqlx.so’; 安装完，会启动一个33060端口X Plugin is a new MySQL Server feature available with MySQL Server 5.7.12 and higher
mysql-shell(客户端） :mysqlsh ，mysqlsh可以连接到33060端口MySQL Shell is an advanced command-line client and code editor for the MySQL Server. In addition to SQL, MySQL Shell also offers scripting capabilities for JavaScript and Python. When MySQL Shell is connected to the MySQL Server through the X Protocol, the X DevAPI can be used to work with both relational and document data.
以前的是存在information_schema中，现在存在performance schema中，show variables和show status也是受影响的。 升级过程中可以用show_compatibility_56做兼容操作，默认关的，需要打开。
5.7多了一个sys schema, 收集一些系统，用于调优和诊断。
MySQL now supports stacked diagnostics areas. 用于存储过程抓异常信息。
可以通过以下命令获取其他正在执行的session的执行计划
EXPLAIN [options] FOR CONNECTION connection_id;
optimizer hints
以前要修改执行计划有一个方法是通过optimizer_switch参数来改变，在5.7.7以后可以用优化器的hints。 写法如下：
SELECT /+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) / f1 FROM t3 WHERE f1 30 AND f1 < 33;
SELECT /+ BKA(t1) NO_BKA(t2) / * FROM t1 INNER JOIN t2 WHERE …;
SELECT /+ NO_ICP(t1, t2) / * FROM t1 INNER JOIN t2 WHERE …;
SELECT /+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) / * FROM t1 …;
EXPLAIN SELECT /+ NO_ICP(t1) / * FROM t1 WHERE …;
http://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html以前版本对单个需要触发的DML事件，只支持1个触发器，现在支持多个。
Previously, a table could have at most one trigger for each combination of trigger event (INSERT, UPDATE, DELETE) and action time (BEFORE, AFTER). This limitation has been lifted and multiple triggers are permitted.
mysql客户端也支持打log，需要加–syslog,linux默认打到/var/log/message
mysql –syslog -uroot -p
表中的列可以是由其他列生成的。而这一列的值可以是VIRTUAL 或是 STORED的，区别是virtual 类似于视图，没有数据，只有计算公式，需要的时候再计算，和Oralce11g的虚拟列一样。另1个stored的时，每次相关列有DML操作的时候这一列都会去更新存储，数据是落地的，类似于物化视图。 默认是virtual。
http://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html CREATE TABLE triangle ( sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) ); INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);旧版本的mysql客户端在执行Control+C，如果有SQL在运行会中断SQL，没有会退出mysql客户端，新版本也会中断SQL，但是不会退出。
对于以row格式的binlog，使用工具mysqlbinlog时，可以通过参数–rewrite-db修改dbname,使用格式：--rewrite-db='dboldname->dbnewname'
分区表也可以用handler了。
什么是handler? handler有以下特性：
HANDLER is faster than SELECT: A designated storage engine handler object is allocated for the HANDLER ... OPEN. The object is reused for subsequent HANDLER statements for that table; it need not be reinitialized for each one. There is less parsing involved. There is no optimizer or query-checking overhead. The handler interface does not have to provide a consistent look of the data (for example, dirty reads are permitted), so the storage engine can use optimizations that SELECT does not normally permit.hander不是标准的SQL语法，可以降低优化器对SQL的解析与优化开锁，性能比select快40-45%。但是它不是一致性读（如脏读）
The HANDLER ... OPEN statement opens a table, making it accessible using subsequent HANDLER ... READ statements. This table object is not shared by other sessions and is not closed until the session calls HANDLER ... CLOSE or the session terminates.分区表上也支持ICP了。
分区交换支持without validation选项。默认是WITH VALIDATION，如果是with validation，会逐行的去校验是否满足分区边界，有不符合的会报错。without validation不会报错， 速度也很快。如果数据不正确使用without validation会有问题：
CREATE TABLE e ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) ) PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150), PARTITION p3 VALUES LESS THAN (MAXVALUE) ); INSERT INTO e VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black"); mysql> CREATE TABLE e2 LIKE e; Query OK, 0 rows affected (1.34 sec) mysql> ALTER TABLE e2 REMOVE PARTITIONING; mysql> select * from e; +------+-------+-------+ | id | fname | lname | +------+-------+-------+ | 16 | Frank | White | | 1669 | Jim | Smith | | 337 | Mary | Jones | | 2005 | Linda | Black | +------+-------+-------+ INSERT INTO e2 VALUES (51, "Ellen", "McDonald"); mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1 | | p1 | 0 | | p2 | 0 | | p3 | 3 | mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION; Query OK, 0 rows affected (0.81 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ mysql> select * from e; +------+-------+----------+ | id | fname | lname| +------+-------+----------+ | 51 | Ellen | McDonald | | 1669 | Jim | Smith| | 337 | Mary | Jones| | 2005 | Linda | Black| +------+-------+----------+ 4 rows in set (0.00 sec) mysql> select * from e2; +----+-------+-------+ | id | fname | lname | +----+-------+-------+ | 16 | Frank | White | +----+-------+-------+ mysql> select * from e where id=51; Empty set (0.00 sec) mysql> select * from e where fname='ellen'; +----+-------+----------+ | id | fname | lname| +----+-------+----------+ | 51 | Ellen | McDonald | +----+-------+----------+ 1 row in set (0.00 sec) 分区交换是做成功了，全遍历分区表可以查到数据，但是按分区键ID去查，找不到数据，按非分区键可以找到数据。dump减少了锁争用，提高了master的吞吐量。
The master dump thread was refactored to reduce lock contention and improve master throughput. Previous to MySQL 5.7.2, the dump thread took a lock on the binary log whenever reading an event; in MySQL 5.7.2 and later, this lock is held only while reading the position at the end of the last successfully written event. This means both that multiple dump threads are now able to read concurrently from the binary log file, and that dump threads are now able to read while clients are writing to the binary log.
全球化改进，增加了gb18030字符集
MySQL 5.7.4 includes a gb18030 character set that supports the China National Standard GB18030 character set.
以前的版本要执行change master to 命令前必须要执行stop slave,现在不需要执行,现在取决于IO线程和SQL线程的运行状态。
If the SQL thread is stopped, you can execute CHANGE MASTER TO using any combination of RELAY_LOG_FILE, RELAY_LOG_POS, and MASTER_DELAY options, even if the slave I/O thread is running. No other options may be used with this statement when the I/O thread is running. If the I/O thread is stopped, you can execute CHANGE MASTER TO using any of the options for this statement (in any allowed combination) except RELAY_LOG_FILE, RELAY_LOG_POS, or MASTER_DELAY, even when the SQL thread is running. These three options may not be used when the I/O thread is running. Both the SQL thread and the I/O thread must be stopped before issuing CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1.The MySQL test suite now uses InnoDB as the default storage engine.
test suite是mysql的自动测试框架，主要用于mysql找了patch后，通过执行一个case得到一个结果，然后与标准输出的结果做比对，没有差异就通过了。
http://dev.mysql.com/doc/mysqltest/2.0/en/preface.html
MySQL也支持多主复制了。
增加了几个组复制相关的表。 MySQL 5.7 adds a number of new tables to the Performance Schema to provide information about replication groups and channels. These include the following tables:
replication_applier_configuration replication_applier_status replication_applier_status_by_coordinator replication_applier_status_by_worker replication_connection_configuration replication_connection_status replication_group_members replication_group_member_stats增加了组复制相关的SQL命令。 The following statements were added in MySQL 5.7.6 for controlling group replication:
START GROUP_REPLICATION STOP GROUP_REPLICATION以下特性在5.7里已经deprecated，在后续的版本可能会移除。
执行CREATE USER and GRANT时，不要用IDENTIFIED BY PASSWORD ‘hash_string’, 要用 IDENTIFIED WITH auth_plugin AS 'hash_string' for 。正确的姿势如下：
create user ‘test1’@’%’ identified with mysql_native_password by ‘123’; create user ‘test1’@’%’ identified with mysql_native_password as ‘23AE809DDACAF96AF0FD78ED04B6A265E05AA257’
password()函数弃用，因此SET PASSWORD … = PASSWORD(‘auth_string’) 也弃用了，但是 SET PASSWORD … = ‘auth_string’没有弃用。无论如何标准推荐用法是alter user.
参数 old_passwords是弃用的。将来会被移除。在5.7group by 是不排序的，如果要排序就使用order by 。
这个参数的作用是支持2阶段提交的分布式事物，在5.7永远是启用的。
可以用character_set_server 和collation_server
下面例子是做了一次加密，又做了次解密： select AES_DECRYPT(aes_encrypt(‘test’,’salt1’),’salt1’) ;
替代的方法是：loading plugins at server startup using the –plugin-load or –plugin-load-add option, or at runtime using the INSTALL PLUGIN statement.
The same is true of the corresponding COM_PROCESS_KILL, COM_FIELD_LIST, COM_PROCESS_INFO, and COM_REFRESH client/server protocol commands. Instead, use mysql_query() to execute a KILL, SHOW COLUMNS, SHOW PROCESSLIST, or FLUSH statement, respectively.
另外，DELAYED相关的几个选项也移除了。
The –delayed-insert option for mysqldump.The COUNT_WRITE_DELAYED, SUM_TIMER_WRITE_DELAYED, MIN_TIMER_WRITE_DELAYED, AVG_TIMER_WRITE_DELAYED, and MAX_TIMER_WRITE_DELAYED columns of the Performance Schema table_lock_waits_summary_by_table table.mysqlbinlog no longer writes comments mentioning INSERT DELAYED.因为这人操作系统用mklink创建的链接冗余。任何.sym的链接都应该用mklink方式替换。
因为有些命令写前辍代表的含义不太清楚，即使现在清楚，也许以后再新增一个相似的选项后，又会变得不清楚。如，以下命令选项必须写全：
The –key-buffer option must now be specified as –key-buffer-size.The –skip-grant option must now be specified as –skip-grant-tables.更多信息：http://dev.mysql.com/doc/refman/5.7/en/innodb-monitors.html
代替方案是用mysqldump and MySQL Enterprise Backup.（其实xtrabackup更好）
