Spec-Zone .ru
спецификации, руководства, описания, API

5.1.7. Server SQL Modes

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients. This capability enables each application to tailor the server's operating mode to its own requirements.

For answers to some questions that are often asked about server SQL modes in MySQL, see Section B.3, "MySQL 5.6 FAQ: Server SQL Mode".

Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

When working with InnoDB tables, consider also the innodb_strict_mode configuration option. It enables additional error checks for InnoDB tables, as listed in Section 14.2.5.7, "InnoDB Strict Mode".

Setting the SQL Mode

You can set the default SQL mode by starting mysqld with the --sql-mode="modes" option, or by using sql-mode="modes" in my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by comma (",") characters. The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set). To clear the mode explicitly, set it to an empty string using --sql-mode="" on the command line, or with sql-mode="" in my.cnf on Unix systems or in my.ini on Windows.

You can change the SQL mode at runtime by using a SET [GLOBAL|SESSION] sql_mode='modes' statement to set the sql_mode system value. 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. Any client can change its own session sql_mode value at any time.

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.

See Section 18.6, "Restrictions and Limitations on Partitioning", for more information.

You can retrieve the current global or session sql_mode value with the following statements:

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

Most Important SQL Modes

The most important sql_mode values are probably these:

When this manual refers to "strict mode," it means a mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.

Full List of SQL Modes

The following list describes all supported modes:

Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back.

For nontransactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:

Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero month or day parts such as '2004-04-00' or "zero" dates. To disallow these as well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in addition to strict mode.

If you are not using strict mode (that is, neither STRICT_TRANS_TABLES nor STRICT_ALL_TABLES is enabled), MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE. See Section 13.7.5.41, "SHOW WARNINGS Syntax".

Strict mode does not affect whether foreign key constraints are checked. foreign_key_checks can be used for that. (See Section 5.1.4, "Server System Variables".)

Combination Modes

The following special modes are provided as shorthand for combinations of mode values from the preceding list.

The descriptions include all mode values that are available in the most recent version of MySQL. For older versions, a combination mode does not include individual mode values that are not available except in newer versions.