MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes (original) (raw)

5.1.10 Server SQL Modes

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.

Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

For answers to questions often asked about server SQL modes in MySQL, see Section A.3, “MySQL 5.7 FAQ: Server SQL Mode”.

When working with InnoDB tables, consider also the innodb_strict_mode system variable. It enables additional error checks forInnoDB tables.

Setting the SQL Mode

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER, andNO_ENGINE_SUBSTITUTION.

These modes were added to the default SQL mode in MySQL 5.7: TheONLY_FULL_GROUP_BY andSTRICT_TRANS_TABLES modes were added in MySQL 5.7.5. TheNO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. TheERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE, andNO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For additional discussion regarding these changes to the default SQL mode value, seeSQL Mode Changes in MySQL 5.7.

To set the SQL mode at server startup, use the--sql-mode="modes" option on the command line, orsql-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.

Note

MySQL installation programs may configure the SQL mode during the installation process. If the SQL mode differs from the default or from what you expect, check for a setting in an option file that the server reads at startup.

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

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

Setting the GLOBAL variable requires theSUPER 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 sessionsql_mode value at any time.

To determine the current global or sessionsql_mode setting, select its value:

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 the source and replica can also lead to problems. For best results, you should always use the same server SQL mode on the source and replica.

For more information, seeSection 22.6, “Restrictions and Limitations on Partitioning”.

The Most Important SQL Modes

The most important sql_mode values are probably these:

When this manual refers to “strict mode,” it means a mode with either or bothSTRICT_TRANS_TABLES orSTRICT_ALL_TABLES enabled.

Full List of SQL Modes

The following list describes all supported SQL modes:

mysql> SET sql_mode = '';  
mysql> SELECT NOT 1 BETWEEN -5 AND 5;  
        -> 0  
mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';  
mysql> SELECT NOT 1 BETWEEN -5 AND 5;  
        -> 1  
mysql> CREATE TABLE count (i INT);  
ERROR 1064 (42000): You have an error in your SQL syntax

The table name should be quoted: 

mysql> CREATE TABLE `count` (i INT);  
Query OK, 0 rows affected (0.00 sec)

The IGNORE_SPACE SQL mode applies to built-in functions, not to loadable functions or stored functions. It is always permissible to have spaces after a loadable function or stored function name, regardless of whetherIGNORE_SPACE is enabled.
For further discussion ofIGNORE_SPACE, seeSection 9.2.5, “Function Name Parsing and Resolution”.

mysql> SET sql_mode = '';  
Query OK, 0 rows affected (0.00 sec)  
mysql> SELECT CAST(0 AS UNSIGNED) - 1;  
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

If theNO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative: 

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';  
mysql> SELECT CAST(0 AS UNSIGNED) - 1;  
+-------------------------+  
| CAST(0 AS UNSIGNED) - 1 |  
+-------------------------+  
|                      -1 |  
+-------------------------+

If the result of such an operation is used to update anUNSIGNED integer column, the result is clipped to the maximum value for the column type, or clipped to 0 ifNO_UNSIGNED_SUBTRACTION is enabled. With strict SQL mode enabled, an error occurs and the column remains unchanged.
WhenNO_UNSIGNED_SUBTRACTION is enabled, the subtraction result is signed, even if any operand is unsigned. For example, compare the type of column c2 in tablet1 with that of columnc2 in table t2: 

mysql> SET sql_mode='';  
mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);  
mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;  
mysql> DESCRIBE t1;  
+-------+---------------------+------+-----+---------+-------+  
| Field | Type                | Null | Key | Default | Extra |  
+-------+---------------------+------+-----+---------+-------+  
| c2    | bigint(21) unsigned | NO   |     | 0       |       |  
+-------+---------------------+------+-----+---------+-------+  
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';  
mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;  
mysql> DESCRIBE t2;  
+-------+------------+------+-----+---------+-------+  
| Field | Type       | Null | Key | Default | Extra |  
+-------+------------+------+-----+---------+-------+  
| c2    | bigint(21) | NO   |     | 0       |       |  
+-------+------------+------+-----+---------+-------+

This means that BIGINT UNSIGNED is not 100% usable in all contexts. SeeSection 12.10, “Cast Functions and Operators”.

mysql> CREATE TABLE t1 (c1 CHAR(10));  
Query OK, 0 rows affected (0.37 sec)  
mysql> INSERT INTO t1 (c1) VALUES('xy');  
Query OK, 1 row affected (0.01 sec)  
mysql> SET sql_mode = '';  
Query OK, 0 rows affected (0.00 sec)  
mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;  
+------+-----------------+  
| c1   | CHAR_LENGTH(c1) |  
+------+-----------------+  
| xy   |               2 |  
+------+-----------------+  
1 row in set (0.00 sec)  
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';  
Query OK, 0 rows affected (0.00 sec)  
mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;  
+------------+-----------------+  
| c1         | CHAR_LENGTH(c1) |  
+------------+-----------------+  
| xy         |              10 |  
+------------+-----------------+  
1 row in set (0.00 sec)

Combination SQL Modes

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

SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2 WHERE ...);

Here, MAX(t1.b) cannot aggregated in the outer query because it appears in theWHERE clause of that query. Standard SQL requires an error in this situation. IfANSI mode is not enabled, the server treats_`S`_(_`outerref`_) in such queries the same way that it would interpret_`S`_(_`const`_).
See Section 1.6, “MySQL Standards Compliance”.

Strict SQL Mode

Strict mode controls how MySQL handles invalid or missing values in data-change statements such asINSERT orUPDATE. 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 explicitDEFAULT clause in its definition. (For aNULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings (seeSection 13.7.5.40, “SHOW WARNINGS Statement”). In strict mode, you can produce this behavior by usingINSERT IGNORE or UPDATE IGNORE.

For statements such as SELECT that do not change data, invalid values generate a warning in strict mode, not an error.

Strict mode produces an error for attempts to create a key that exceeds the maximum key length. When strict mode is not enabled, this results in a warning and truncation of the key to the maximum key length.

Strict mode does not affect whether foreign key constraints are checked. foreign_key_checks can be used for that. (SeeSection 5.1.7, “Server System Variables”.)

Strict SQL mode is in effect if eitherSTRICT_ALL_TABLES orSTRICT_TRANS_TABLES is enabled, although the effects of these modes differ somewhat:

Strict mode affects handling of division by zero, zero dates, and zeros in dates as follows:

For more information about strict mode with respect toIGNORE, seeComparison of the IGNORE Keyword and Strict SQL Mode.

Before MySQL 5.7.4, and in MySQL 5.7.8 and later, strict mode affects handling of division by zero, zero dates, and zeros in dates in conjunction with theERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE, andNO_ZERO_IN_DATE modes. From MySQL 5.7.4 though 5.7.7, theERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE, andNO_ZERO_IN_DATE modes do nothing when named explicitly and their effects are included in the effects of strict mode. For additional discussion, seeSQL Mode Changes in MySQL 5.7.

Comparison of the IGNORE Keyword and Strict SQL Mode

This section compares the effect on statement execution of theIGNORE keyword (which downgrades errors to warnings) and strict SQL mode (which upgrades warnings to errors). It describes which statements they affect, and which errors they apply to.

The following table presents a summary comparison of statement behavior when the default is to produce an error versus a warning. An example of when the default is to produce an error is inserting a NULL into a NOT NULL column. An example of when the default is to produce a warning is inserting a value of the wrong data type into a column (such as inserting the string'abc' into an integer column).

Operational Mode When Statement Default is Error When Statement Default is Warning
Without IGNORE or strict SQL mode Error Warning
With IGNORE Warning Warning (same as without IGNORE or strict SQL mode)
With strict SQL mode Error (same as without IGNORE or strict SQL mode) Error
With IGNORE and strict SQL mode Warning Warning

One conclusion to draw from the table is that when theIGNORE keyword and strict SQL mode are both in effect, IGNORE takes precedence. This means that, although IGNORE and strict SQL mode can be considered to have opposite effects on error handling, they do not cancel when used together.

The Effect of IGNORE on Statement Execution

Several statements in MySQL support an optionalIGNORE keyword. This keyword causes the server to downgrade certain types of errors and generate warnings instead. For a multiple-row statement, downgrading an error to a warning may enable a row to be processed. Otherwise,IGNORE causes the statement to skip to the next row instead of aborting. (For nonignorable errors, an error occurs regardless of the IGNORE keyword.)

Example: If the table t has a primary key column i containing unique values, attempting to insert the same value of i into multiple rows normally produces a duplicate-key error:

mysql> CREATE TABLE t (i INT NOT NULL PRIMARY KEY);
mysql> INSERT INTO t (i) VALUES(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

With IGNORE, the row containing the duplicate key still is not inserted, but a warning occurs instead of an error:

mysql> INSERT IGNORE INTO t (i) VALUES(1),(1);
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

Example: If the table t2 has a NOT NULL column id, attempting to insert NULL produces an error in strict SQL mode:

mysql> CREATE TABLE t2 (id INT NOT NULL);
mysql> INSERT INTO t2 (id) VALUES(1),(NULL),(3);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> SELECT * FROM t2;
Empty set (0.00 sec)

If the SQL mode is not strict, IGNORE causes the NULL to be inserted as the column implicit default (0 in this case), which enables the row to be handled without skipping it:

mysql> INSERT INTO t2 (id) VALUES(1),(NULL),(3);
mysql> SELECT * FROM t2;
+----+
| id |
+----+
|  1 |
|  0 |
|  3 |
+----+

These statements support the IGNORE keyword:

The IGNORE keyword applies to the following ignorable errors:

The Effect of Strict SQL Mode on Statement Execution

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. In “strict” SQL mode, the server upgrades certain warnings to errors.

For example, in non-strict SQL mode, inserting the string'abc' into an integer column results in conversion of the value to 0 and a warning:

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (i) VALUES('abc');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

In strict SQL mode, the invalid value is rejected with an error:

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (i) VALUES('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1

For more information about possible settings of thesql_mode system variable, seeSection 5.1.10, “Server SQL Modes”.

Strict SQL mode applies to the following statements under conditions for which some value might be out of range or an invalid row is inserted into or deleted from a table:

Within stored programs, individual statements of the types just listed execute in strict SQL mode if the program was defined while strict mode was in effect.

Strict SQL mode applies to the following errors, which represent a class of errors in which an input value is either invalid or missing. A value is invalid if it has the wrong data type for the column or might be out of range. A value is missing if a new row to be inserted does not contain a value for a NOT NULL column that has no explicitDEFAULT clause in its definition.

ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED

Note

Because continued MySQL development defines new errors, there may be errors not in the preceding list to which strict SQL mode applies.

SQL Mode Changes in MySQL 5.7

In MySQL 5.7.22, these SQL modes are deprecated and are removed in MySQL 8.0: DB2,MAXDB,MSSQL,MYSQL323,MYSQL40,ORACLE,POSTGRESQL,NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS.

In MySQL 5.7, theONLY_FULL_GROUP_BY SQL mode is enabled by default because GROUP BY processing has become more sophisticated to include detection of functional dependencies. However, if you find that havingONLY_FULL_GROUP_BY enabled causes queries for existing applications to be rejected, either of these actions should restore operation:

In MySQL 5.7, theERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE, andNO_ZERO_IN_DATE SQL modes are deprecated. The long term plan is to have the three modes be included in strict SQL mode and to remove them as explicit modes in a future release of MySQL. For compatibility in MySQL 5.7 with MySQL 5.6 strict mode and to provide additional time for affected applications to be modified, the following behaviors apply:

With the preceding changes, stricter data checking is still enabled by default, but the individual modes can be disabled in environments where it is currently desirable or necessary to do so.