Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.3Mb
PDF (A4) - 35.3Mb
PDF (RPM) - 34.3Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.4Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 305.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

6.1.7 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 for InnoDB 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, and NO_ENGINE_SUBSTITUTION.

The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For additional discussion regarding these changes to the default SQL mode value, see SQL Mode Changes in MySQL 5.7.

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.

Note

MySQL installation programs may configure the SQL mode during the installation process. For example, mysql_install_db creates a default option file named my.cnf in the base installation directory. This file contains a line that sets the SQL mode; see Section 5.4.2, “mysql_install_db — Initialize MySQL Data Directory”.

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 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.

See Section 20.6, “Restrictions and Limitations on Partitioning”, for more information.

The Most Important SQL Modes

The most important sql_mode values are probably these:

  • 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.

  • 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.

    Note

    The INSERT or UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a nontransactional storage engine, because data changes made prior to the error may not be rolled back, resulting in a partially done update.

When this manual refers to strict mode, it means a mode with either or both STRICT_TRANS_TABLES or STRICT_ALL_TABLES enabled.

Full List of SQL Modes

The following list describes all supported SQL modes:

  • ALLOW_INVALID_DATES

    Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation). This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.

    The server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES.

  • ANSI_QUOTES

    Treat " as an identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

  • ERROR_FOR_DIVISION_BY_ZERO

    The ERROR_FOR_DIVISION_BY_ZERO mode affects handling of division by zero, which includes MOD(N,0). For data-change operations (INSERT, UPDATE), its effect also depends on whether strict SQL mode is enabled.

    • If this mode is not enabled, division by zero inserts NULL and produces no warning.

    • If this mode is enabled, division by zero inserts NULL and produces a warning.

    • If this mode and strict mode are enabled, division by zero produces an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, division by zero inserts NULL and produces a warning.

    For SELECT, division by zero returns NULL. Enabling ERROR_FOR_DIVISION_BY_ZERO causes a warning to be produced as well, regardless of whether strict mode is enabled.

    As of MySQL 5.7.4, ERROR_FOR_DIVISION_BY_ZERO is deprecated. In MySQL 5.7.4 through 5.7.7, ERROR_FOR_DIVISION_BY_ZERO does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, ERROR_FOR_DIVISION_BY_ZERO does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if ERROR_FOR_DIVISION_BY_ZERO is enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7.

    Because ERROR_FOR_DIVISION_BY_ZERO is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

  • HIGH_NOT_PRECEDENCE

    The precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). In some older versions of MySQL, the expression was parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behavior can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode.

    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
    
  • IGNORE_SPACE

    Permit spaces between a function name and the ( character. This causes built-in function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 10.2, “Schema Object Names”. For example, because there is a COUNT() function, the use of count as a table name in the following statement causes an error:

    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 user-defined functions or stored functions. It is always permissible to have spaces after a UDF or stored function name, regardless of whether IGNORE_SPACE is enabled.

    For further discussion of IGNORE_SPACE, see Section 10.2.4, “Function Name Parsing and Resolution”.

  • NO_AUTO_CREATE_USER

    Prevent the GRANT statement from automatically creating new user accounts if it would otherwise do so, unless authentication information is specified. The statement must specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH.

    It is preferable to create MySQL accounts with CREATE USER rather than GRANT. As of MySQL 5.7.6, NO_AUTO_CREATE_USER is deprecated. As of 5.7.7 the default SQL mode includes NO_AUTO_CREATE_USER and assignments to sql_mode that change the NO_AUTO_CREATE_USER mode state produce a warning, except assignments that set sql_mode to DEFAULT. NO_AUTO_CREATE_USER will be removed in a future MySQL release, at which point its effect will be enabled at all times (GRANT will not create accounts).

  • NO_AUTO_VALUE_ON_ZERO

    NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

    This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

  • NO_BACKSLASH_ESCAPES

    Disable the use of the backslash character (\) as an escape character within strings. With this mode enabled, backslash becomes an ordinary character like any other.

  • NO_DIR_IN_CREATE

    When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers.

  • 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.

    The default SQL mode includes NO_ENGINE_SUBSTITUTION.

    Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:

    With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

    With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.

  • NO_FIELD_OPTIONS

    Do not print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

  • NO_KEY_OPTIONS

    Do not print MySQL-specific index options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

  • NO_TABLE_OPTIONS

    Do not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

  • NO_UNSIGNED_SUBTRACTION

    Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, an error results:

    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 the NO_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 an UNSIGNED integer column, the result is clipped to the maximum value for the column type, or clipped to 0 if NO_UNSIGNED_SUBTRACTION is enabled. If strict SQL mode is enabled, an error occurs and the column remains unchanged.

    When NO_UNSIGNED_SUBTRACTION is enabled, the subtraction result is signed, even if any operand is unsigned. For example, compare the type of column c2 in table t1 with that of column c2 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. See Section 13.10, “Cast Functions and Operators”.

  • NO_ZERO_DATE

    The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

    • If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.

    • If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.

    • If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

    As of MySQL 5.7.4, NO_ZERO_DATE is deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_DATE does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_DATE does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_DATE is enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7.

    Because NO_ZERO_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

  • NO_ZERO_IN_DATE

    The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'. To control whether the server permits '0000-00-00', use the NO_ZERO_DATE mode.) The effect of NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled.

    • If this mode is not enabled, dates with zero parts are permitted and inserts produce no warning.

    • If this mode is enabled, dates with zero parts are inserted as '0000-00-00' and produce a warning.

    • If this mode and strict mode are enabled, dates with zero parts are not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, dates with zero parts are inserted as '0000-00-00' and produce a warning.

    As of MySQL 5.7.4, NO_ZERO_IN_DATE is deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_IN_DATE does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_IN_DATE does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_IN_DATE is enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7.

    Because NO_ZERO_IN_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

  • ONLY_FULL_GROUP_BY

    Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

    As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

    A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.

    For additional discussion and examples, see Section 13.20.3, “MySQL Handling of GROUP BY”.

  • PAD_CHAR_TO_FULL_LENGTH

    By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval.

    
    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)
    
  • PIPES_AS_CONCAT

    Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR.

  • REAL_AS_FLOAT

    Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE.

  • STRICT_ALL_TABLES

    Enable strict SQL mode for all storage engines. Invalid data values are rejected. For details, see Strict SQL Mode.

    From MySQL 5.7.4 through 5.7.7, STRICT_ALL_TABLES includes the effect of the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes. For additional discussion, see SQL Mode Changes in MySQL 5.7.

  • STRICT_TRANS_TABLES

    Enable strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines. For details, see Strict SQL Mode.

    From MySQL 5.7.4 through 5.7.7, STRICT_TRANS_TABLES includes the effect of the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes. For additional discussion, see SQL Mode Changes in MySQL 5.7.

Combination SQL Modes

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

Strict SQL Mode

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. 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.) 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 (see Section 14.7.5.40, “SHOW WARNINGS Syntax”). In strict mode, you can produce this behavior by using INSERT 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. (See Section 6.1.4, “Server System Variables”.)

Strict SQL mode is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled, although the effects of these modes differ somewhat:

  • For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is 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 mode is enabled:

    • For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

    • For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 12.7, “Data Type Default Values”.

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

  • Strict mode affects handling of division by zero, which includes MOD(N,0):

    For data-change operations (INSERT, UPDATE):

    • If strict mode is not enabled, division by zero inserts NULL and produces no warning.

    • If strict mode is enabled, division by zero produces an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, division by zero inserts NULL and produces a warning.

    For SELECT, division by zero returns NULL. Enabling strict mode causes a warning to be produced as well.

  • Strict mode affects whether the server permits '0000-00-00' as a valid date:

    • If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.

    • If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

  • Strict mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0 (dates such as '2010-00-01' or '2010-01-00'):

    • If strict mode is not enabled, dates with zero parts are permitted and inserts produce no warning.

    • If strict mode is enabled, dates with zero parts are not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, dates with zero parts are inserted as '0000-00-00' (which is considered valid with IGNORE) and produce a warning.

For more information about strict mode with respect to IGNORE, see Comparison 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 the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes. From MySQL 5.7.4 though 5.7.7, the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes do nothing when named explicitly and their effects are included in the effects of strict mode. For additional discussion, see SQL Mode Changes in MySQL 5.7.

Comparison of the IGNORE Keyword and Strict SQL Mode

This section compares the effect on statement execution of the IGNORE 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 ModeWhen Statement Default is ErrorWhen Statement Default is Warning
Without IGNORE or strict SQL modeErrorWarning
With IGNOREWarningWarning (same as without IGNORE or strict SQL mode)
With strict SQL modeError (same as without IGNORE or strict SQL mode)Error
With IGNORE and strict SQL modeWarningWarning

One conclusion to draw from the table is that when the IGNORE 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 optional IGNORE keyword. This keyword causes the server to downgrade certain types of errors and generate warnings instead. For a multiple-row statement, IGNORE causes the statement to skip to the next row instead of aborting.

For example, if the table t has a primary key column i, attempting to insert the same value of i into multiple rows normally produces a duplicate-key error:

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)

These statements support the IGNORE keyword:

  • CREATE TABLE ... SELECT: IGNORE does not apply to the CREATE TABLE or SELECT parts of the statement but to inserts into the table of rows produced by the SELECT. Rows that duplicate an existing row on a unique key value are discarded.

  • DELETE: IGNORE causes MySQL to ignore errors during the process of deleting rows.

  • INSERT: With IGNORE, rows that duplicate an existing row on a unique key value are discarded. Rows set to values that would cause data conversion errors are set to the closest valid values instead.

    For partitioned tables where no partition matching a given value is found, IGNORE causes the insert operation to fail silently for rows containing the unmatched value.

  • LOAD DATA, LOAD XML: With IGNORE, rows that duplicate an existing row on a unique key value are discarded.

  • UPDATE: With IGNORE, rows for which duplicate-key conflicts occur on a unique key value are not updated. Rows updated to values that would cause data conversion errors are updated to the closest valid values instead.

The IGNORE keyword applies to the following errors:

ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED
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 the sql_mode system variable, see Section 6.1.7, “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, 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 explicit DEFAULT 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

SQL Mode Changes in MySQL 5.7

In MySQL 5.7.5, the ONLY_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 having ONLY_FULL_GROUP_BY enabled causes queries for existing applications to be rejected, either of these actions should restore operation:

  • If it is possible to modify an offending query, do so, either so that nonaggregated columns are functionally dependent on GROUP BY columns, or by referring to nonaggregated columns using ANY_VALUE().

  • If it is not possible to modify an offending query (for example, if it is generated by a third-party application), set the sql_mode system variable at server startup to not enable ONLY_FULL_GROUP_BY.

As of MySQL 5.7.4, the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes are deprecated. From MySQL 5.7.4 though 5.7.7, these modes do nothing when named explicitly. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). In other words, strict mode means the same thing in those versions as the pre-5.7.4 meaning of strict mode plus ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE.

The MySQL 5.7.4 change to make strict mode more strict by including ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE caused some problems. For example, in MySQL 5.6 with strict mode but not NO_ZERO_DATE enabled, TIMESTAMP columns can be defined with DEFAULT '0000-00-00 00:00:00'. In MySQL 5.7.4 with the same mode settings, strict mode includes the effect of NO_ZERO_DATE and TIMESTAMP columns cannot be defined with DEFAULT '0000-00-00 00:00:00'. This causes replication of CREATE TABLE statements from 5.6 to 5.7.4 to fail if they contain such TIMESTAMP columns.

The long term plan is still to have the three affected modes be included in strict SQL mode and to remove them as explicit modes in a future MySQL release. But to restore compatibility in MySQL 5.7 with MySQL 5.6 strict mode and to provide additional time for affected applications to be modified, the following changes were made in MySQL 5.7.8:

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.

Although in MySQL 5.7.8 and later ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE can be used separately from strict mode, it is intended that they be used together. As a reminder, a warning occurs if they are enabled without also enabling strict mode or vice versa.

Important

The following discussion applies only for MySQL versions 5.7.4 through 5.7.7. For upgrades from a version older than MySQL 5.7.4, we recommend upgrading to MySQL 5.7.8 or later, which renders this discussion unnecessary.

The remainder of this section describes the SQL mode settings to use in MySQL 5.7.4 through 5.7.7 to achieve the same statement execution as before 5.7.4, including the cases for INSERT and UPDATE in which IGNORE is given. It also provides guidelines for determining whether applications need modification to behave the same before and after the SQL mode changes.

The following table shows how to control handling of division by zero for versions other than MySQL 5.7.4 through 5.7.7 and for MySQL 5.7.4 through 5.7.7.

Desired BehaviorMySQL 5.7.x Versions Except 5.7.4 Through 5.7.7MySQL 5.7.4 Through 5.7.7
insert NULL, produce no warningERROR_FOR_DIVISION_BY_ZERO not enabledstrict mode not enabled
insert NULL, produce warningERROR_FOR_DIVISION_BY_ZERO, or ERROR_FOR_DIVISION_BY_ZERO + strict mode + IGNOREstrict mode + IGNORE
errorERROR_FOR_DIVISION_BY_ZERO + strict modestrict mode

The following table shows how to control whether the server permits '0000-00-00' as a valid date for versions other than MySQL 5.7.4 through 5.7.7 and for MySQL 5.7.4 through 5.7.7.

Desired BehaviorMySQL 5.7.x Versions Except 5.7.4 Through 5.7.7MySQL 5.7.4 Through 5.7.7
insert '0000-00-00', produce no warningNO_ZERO_DATE not enabledstrict mode not enabled
insert '0000-00-00', produce warningNO_ZERO_DATE, or NO_ZERO_DATE + strict mode + IGNOREstrict mode + IGNORE
errorNO_ZERO_DATE + strict modestrict mode

The following table shows how to control whether the server permits dates with zero parts for versions other than MySQL 5.7.4 through 5.7.7 and for MySQL 5.7.4 through 5.7.7.

Desired BehaviorMySQL 5.7.x Versions Except 5.7.4 Through 5.7.7MySQL 5.7.4 Through 5.7.7
insert date, produce no warningNO_ZERO_IN_DATE not enabledstrict mode not enabled
insert '0000-00-00', produce warningNO_ZERO_IN_DATE, or NO_ZERO_IN_DATE + strict mode + IGNOREstrict mode + IGNORE
errorNO_ZERO_IN_DATE + strict modestrict mode

The following discussion describes the conditions under which a given statement produces the same or different result under the SQL mode changes in MySQL 5.7.4 through 5.7.7. It considers only strict mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the three deprecated modes (ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE). Other SQL modes such as ANSI_QUOTES or ONLY_FULL_GROUP_BY are assumed to be held constant before and after an upgrade.

This discussion also describes how to prepare for an upgrade to 5.7.4 through 5.7.7 from a version older than 5.7.4. Any modifications should be made before upgrading.

There is no change in behavior between MySQL 5.6 and 5.7 for the following SQL mode settings. A statement that executes under one of these settings needs no modification to produce the same result in 5.6 and 5.7:

  • Strict mode and the three deprecated modes are all not enabled.

  • Strict mode and the three deprecated modes are all enabled.

A change from warnings in MySQL 5.6 to no warnings in MySQL 5.7 occurs for the following SQL mode settings. The result of statement execution is the same in 5.6 and 5.7, so statements need no modification unless warnings are considered significant:

A behavior change occurs under the following SQL mode settings. A statement that executes under one of these settings must be modified to produce the same result in 5.6 and 5.7:

  • Strict mode is not enabled, NO_ZERO_IN_DATE is enabled. For this mode setting, expect these differences in statement execution:

    • In 5.6, the server inserts dates with zero parts as '0000-00-00' and produces a warning.

    • In 5.7, the server inserts dates with zero parts as is and produces no warning.

  • Strict mode is enabled, with some but not all of the three deprecated modes enabled. For this mode setting, expect these differences in statement execution:

    Statements that would be affected by enabling the not-enabled deprecated modes produce errors in 5.7 but not in 5.6. Suppose that strict mode, NO_ZERO_DATE, and NO_ZERO_IN_DATE are enabled, and a data-change statement performs division by zero:

To prepare for an upgrade to MySQL 5.7.4 through 5.7.7, the main principle is to make sure that your applications will operate the same way in MySQL 5.6 and 5.7. For example, you can adopt either of these approaches to application compatibility:

  • Modify the application to set the SQL mode on a version-specific basis. If we assume that an application will not be used with development versions of MySQL 5.7 prior to 5.7.4, it is possible to set the sql_mode value for the application based on the current server version as follows:

    SET sql_mode = IF(LEFT(VERSION(),3)<'5.7',5.6 mode,5.7 mode);
    

    The tables shown earlier in this section serve as a guide to the appropriate equivalent modes for MySQL 5.6 and 5.7.

  • Modify the application to execute under an SQL mode for which statements produce the same result in MySQL 5.6 and 5.7.

    Tip

    TRADITIONAL SQL mode in MySQL 5.6 includes strict mode and the three deprecated modes. If you write applications to operate in TRADITIONAL mode in MySQL 5.6, there is no change to make for MySQL 5.7.

When assessing SQL mode compatibility between MySQL 5.6 and 5.7, consider particularly these statement execution contexts:

  • Replication. You will encounter replication incompatibility related to the SQL mode changes under the following conditions:

    • MySQL 5.6 master and 5.7 slave

    • Statement-based replication

    • An SQL mode setting for which statements produce different results in MySQL 5.6 and 5.7, as described earlier

    To handle this incompatibility, use one of these workarounds:

    • Use row-based replication

    • Use IGNORE

    • Use an SQL mode for which statements do not produce different results in MySQL 5.6 and 5.7

  • Stored programs (stored procedures and functions, triggers, and events). Each stored program executes using the SQL mode in effect at the time it was created. To identify stored programs that may be affected by differences between MySQL 5.6 and 5.7 in SQL mode handling, use these queries:

    SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, SQL_MODE
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE SQL_MODE LIKE '%STRICT%'
    OR SQL_MODE LIKE '%DIVISION%'
    OR SQL_MODE LIKE '%NO_ZERO%';
    
    SELECT TRIGGER_SCHEMA, TRIGGER_NAME, SQL_MODE
    FROM INFORMATION_SCHEMA.TRIGGERS
    WHERE SQL_MODE LIKE '%STRICT%'
    OR SQL_MODE LIKE '%DIVISION%'
    OR SQL_MODE LIKE '%NO_ZERO%';
    
    SELECT EVENT_SCHEMA, EVENT_NAME, SQL_MODE
    FROM INFORMATION_SCHEMA.EVENTS
    WHERE SQL_MODE LIKE '%STRICT%'
    OR SQL_MODE LIKE '%DIVISION%'
    OR SQL_MODE LIKE '%NO_ZERO%';
    

User Comments
  Posted by Mehdi Salarkia on October 29, 2014
Just a FYI that by setting NO_BACKSLASH_ESCAPES does not mean that you are skipping the '\' when it is used in LIKE queries.
Make sure to read this note :

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

Exception: At the end of the pattern string, backslash can be specified as “\\”. At the end of the string, backslash stands for itself because there is nothing following to escape.

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Sign Up Login You must be logged in to post a comment.