mysql: sql mode

官网原址：https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes

The default SQL mode in MySQL 5.7 includes these modes: 1.) ONLY_FULL_GROUP_BY, 2.) STRICT_TRANS_TABLES, #rpm5.6 3.) NO_ZERO_IN_DATE, 4.) NO_ZERO_DATE, 5.) ERROR_FOR_DIVISION_BY_ZERO, 6.) NO_AUTO_CREATE_USER, 7.) NO_ENGINE_SUBSTITUTION. #这是我rmp装5.6 build版默认的同时还有 2.) 下面是5.7的模式的说明： The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. To set the SQL mode at server startup, use the –sql-mode=”modes” option on the command line, or sql-mode=”modes” in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using –sql-mode=”” on the command line, or sql-mode=”” in an option file.

To change the SQL mode at runtime, set the global or session sql_mode system variable using a SET statement:

SET GLOBAL sql_mode = 'modes'; SET SESSION sql_mode = 'modes';

Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Each client can change its session sql_mode value at any time. To determine the current global or session sql_mode value, use the following statements:

SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;

Important

SQL mode and user-defined partitioning. Changing the server SQL mode after creating and inserting data into partitioned tables can cause major changes in the behavior of such tables, and could lead to loss or corruption of data. It is strongly recommended that you never change the SQL mode once you have created tables employing user-defined partitioning. When replicating partitioned tables, differing SQL modes on master and slave can also lead to problems. For best results, you should always use the same server SQL mode on the master and on the slave.

The Most Important SQL Modes

1）ANSI

This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes listed at the end of this section.

2）STRICT_TRANS_TABLES

If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More details are given later in this section.

As of MySQL 5.7.5, the default SQL mode includes STRICT_TRANS_TABLES.

TRADITIONAL

Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. It is one of the special combination modes listed at the end of this section. 来自深入浅出mysql: https://re.jd.com/cps/item/11381295.html?cu=true&utm_source=click.linktech.cn&utm_medium=tuiguang&utm_campaign=t_4_A100230166&utm_term=b5cedfecd23843439b534d3502a93de7&abt=3

还有更多组合，mysql官网有。 今天我要了解的就是

NO_ENGINE_SUBSTITUTION Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.