Movatterモバイル変換

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.

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

• ANSI

  This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the specialcombination 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 includesSTRICT_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 specialcombination modes listed at the end of this section.

  Note

  WithTRADITIONAL mode enabled, anINSERT orUPDATE aborts as soon as an error occurs. If you are using a nontransactional storage engine, this may not be what you want because data changes made prior to the error may not be rolled back, resulting in a“partially done” update.

• 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 may be useful for Web applications that obtain year, month, and day in three different fields and store exactly what the user inserted, without date validation. This mode applies toDATE andDATETIME columns. It does not apply toTIMESTAMP columns, which always require a valid date.

  WithALLOW_INVALID_DATES disabled, 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, enableALLOW_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. WithANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.

• ERROR_FOR_DIVISION_BY_ZERO

  TheERROR_FOR_DIVISION_BY_ZERO mode affects handling of division by zero, which includesMOD(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 insertsNULL and produces no warning.

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

  • If this mode and strict mode are enabled, division by zero produces an error, unlessIGNORE is given as well. ForINSERT IGNORE andUPDATE IGNORE, division by zero insertsNULL and produces a warning.

  ForSELECT, division by zero returnsNULL. EnablingERROR_FOR_DIVISION_BY_ZERO causes a warning to be produced as well, regardless of whether strict mode is enabled.

  ERROR_FOR_DIVISION_BY_ZERO is deprecated.ERROR_FOR_DIVISION_BY_ZERO is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs ifERROR_FOR_DIVISION_BY_ZERO is enabled without also enabling strict mode or vice versa. For additional discussion, seeSQL Mode Changes in MySQL 5.7.

  BecauseERROR_FOR_DIVISION_BY_ZERO is deprecated; expect it to be removed in a future release of MySQL as a separate mode name and its effect included in the effects of strict SQL mode.

• HIGH_NOT_PRECEDENCE

  The precedence of theNOT operator is such that expressions such asNOT a BETWEEN b AND c are parsed asNOT (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 theHIGH_NOT_PRECEDENCE SQL mode.

  mysql> SET sql_mode = '';mysql> SELECT NOT 1 BETWEEN -5 AND 5;        -> 0mysql> 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 inSection 9.2, “Schema Object Names”. For example, because there is aCOUNT() function, the use ofcount 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)

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

• NO_AUTO_CREATE_USER

  Prevent theGRANT 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 usingIDENTIFIED BY or an authentication plugin usingIDENTIFIED WITH.

  It is preferable to create MySQL accounts withCREATE USER rather thanGRANT.NO_AUTO_CREATE_USER is deprecated and the default SQL mode includesNO_AUTO_CREATE_USER. Assignments tosql_mode that change theNO_AUTO_CREATE_USER mode state produce a warning, except assignments that setsql_mode toDEFAULT. ExpectNO_AUTO_CREATE_USER to be be removed in a future release of MySQL, and its effect to be enabled at all times (and forGRANT not to create accounts any longer).

  Previously, beforeNO_AUTO_CREATE_USER was deprecated, one reason not to enable it was that it was not replication safe. Now it can be enabled and replication-safe user management performed withCREATE USER IF NOT EXISTS,DROP USER IF EXISTS, andALTER USER IF EXISTS rather thanGRANT. These statements enable safe replication when replicas may have different grants than those on the source. SeeSection 13.7.1.2, “CREATE USER Statement”,Section 13.7.1.3, “DROP USER Statement”, andSection 13.7.1.1, “ALTER USER Statement”.

• NO_AUTO_VALUE_ON_ZERO

  NO_AUTO_VALUE_ON_ZERO affects handling ofAUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting eitherNULL or0 into it.NO_AUTO_VALUE_ON_ZERO suppresses this behavior for0 so that onlyNULL generates the next sequence number.

  This mode can be useful if0 has been stored in a table'sAUTO_INCREMENT column. (Storing0 is not a recommended practice, by the way.) For example, if you dump the table withmysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the0 values, resulting in a table with contents different from the one that was dumped. EnablingNO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. For this reason,mysqldump automatically includes in its output a statement that enablesNO_AUTO_VALUE_ON_ZERO.

• NO_BACKSLASH_ESCAPES

  Enabling this mode disables the use of the backslash character (\) as an escape character within strings and identifiers. With this mode enabled, backslash becomes an ordinary character like any other, and the default escape sequence forLIKE expressions is changed so that no escape character is used.

• NO_DIR_IN_CREATE

  When creating a table, ignore allINDEX DIRECTORY andDATA DIRECTORY directives. This option is useful on replica replication servers.

• NO_ENGINE_SUBSTITUTION

  Control automatic substitution of the default storage engine when a statement such asCREATE TABLE orALTER TABLE specifies a storage engine that is disabled or not compiled in.

  By default,NO_ENGINE_SUBSTITUTION is enabled.

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

  WithNO_ENGINE_SUBSTITUTION disabled, forCREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. ForALTER TABLE, a warning occurs and the table is not altered.

  WithNO_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 ofSHOW CREATE TABLE. This mode is used bymysqldump in portability mode.

  Note

  As of MySQL 5.7.22,NO_FIELD_OPTIONS is deprecated. It is removed in MySQL 8.0.

• NO_KEY_OPTIONS

  Do not print MySQL-specific index options in the output ofSHOW CREATE TABLE. This mode is used bymysqldump in portability mode.

  Note

  As of MySQL 5.7.22,NO_KEY_OPTIONS is deprecated. It is removed in MySQL 8.0.

• NO_TABLE_OPTIONS

  Do not print MySQL-specific table options (such asENGINE) in the output ofSHOW CREATE TABLE. This mode is used bymysqldump in portability mode.

  Note

  As of MySQL 5.7.22,NO_TABLE_OPTIONS is deprecated. It is removed in MySQL 8.0.

• NO_UNSIGNED_SUBTRACTION

  Subtraction between integer values, where one is of typeUNSIGNED, 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 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 columnc2 in tablet1 with that of columnc2 in tablet2:

  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 thatBIGINT UNSIGNED is not 100% usable in all contexts. SeeSection 12.10, “Cast Functions and Operators”.

• NO_ZERO_DATE

  TheNO_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, unlessIGNORE is given as well. ForINSERT IGNORE andUPDATE IGNORE,'0000-00-00' is permitted and inserts produce a warning.

  NO_ZERO_DATE is deprecated.NO_ZERO_DATE is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs ifNO_ZERO_DATE is enabled without also enabling strict mode or vice versa. For additional discussion, seeSQL Mode Changes in MySQL 5.7.

  BecauseNO_ZERO_DATE is deprecated; expect it to be removed in a future release of MySQL as a separate mode name and its effect included in the effects of strict SQL mode.

• NO_ZERO_IN_DATE

  TheNO_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 theNO_ZERO_DATE mode.) The effect ofNO_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, unlessIGNORE is given as well. ForINSERT IGNORE andUPDATE IGNORE, dates with zero parts are inserted as'0000-00-00' and produce a warning.

  NO_ZERO_IN_DATE is deprecated.NO_ZERO_IN_DATE is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs ifNO_ZERO_IN_DATE is enabled without also enabling strict mode or vice versa. For additional discussion, seeSQL Mode Changes in MySQL 5.7.

  BecauseNO_ZERO_IN_DATE is deprecated; expect it to be removed in a future release of MySQL 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, orORDER BY list refer to nonaggregated columns that are neither named in theGROUP BY clause nor are functionally dependent on (uniquely determined by)GROUP BY columns.

  As of MySQL 5.7.5, the default SQL mode includesONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency andONLY_FULL_GROUP_BY is not enabled by default.)

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

  For additional discussion and examples, seeSection 12.19.3, “MySQL Handling of GROUP BY”.

• PAD_CHAR_TO_FULL_LENGTH

  By default, trailing spaces are trimmed fromCHAR column values on retrieval. IfPAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrievedCHAR values are padded to their full length. This mode does not apply toVARCHAR 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 asCONCAT()) rather than as a synonym forOR.

• REAL_AS_FLOAT

  TreatREAL as a synonym forFLOAT. By default, MySQL treatsREAL as a synonym forDOUBLE.

• STRICT_ALL_TABLES

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

  From MySQL 5.7.4 through 5.7.7,STRICT_ALL_TABLES includes the effect of theERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE, andNO_ZERO_IN_DATE modes. For additional discussion, seeSQL 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, seeStrict SQL Mode.

  From MySQL 5.7.4 through 5.7.7,STRICT_TRANS_TABLES includes the effect of theERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE, andNO_ZERO_IN_DATE modes. For additional discussion, seeSQL Mode Changes in MySQL 5.7.

• ANSI

  Equivalent toREAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE, and (as of MySQL 5.7.5)ONLY_FULL_GROUP_BY.

  ANSI mode also causes the server to return an error for queries where a set functionS with an outer referenceS(outer_ref) cannot be aggregated in the outer query against which the outer reference has been resolved. This is such a query:

  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 treatsS(outer_ref) in such queries the same way that it would interpretS(const).

  SeeSection 1.6, “MySQL Standards Compliance”.

• DB2

  Equivalent toPIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS.

  Note

  As of MySQL 5.7.22,DB2 is deprecated. It is removed in MySQL 8.0.

• MAXDB

  Equivalent toPIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER.

  Note

  As of MySQL 5.7.22,MAXDB is deprecated. It is removed in MySQL 8.0.

• MSSQL

  Equivalent toPIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS.

  Note

  As of MySQL 5.7.22,MSSQL is deprecated. It is removed in MySQL 8.0.

• MYSQL323

  Equivalent toMYSQL323,HIGH_NOT_PRECEDENCE. This meansHIGH_NOT_PRECEDENCE plus someSHOW CREATE TABLE behaviors specific toMYSQL323:

  • TIMESTAMP column display does not includeDEFAULT orON UPDATE attributes.

  • String column display does not include character set and collation attributes. ForCHAR andVARCHAR columns, if the collation is binary,BINARY is appended to the column type.

  • TheENGINE=engine_name table option displays asTYPE=engine_name.

  • ForMEMORY tables, the storage engine is displayed asHEAP.

  Note

  As of MySQL 5.7.22,MYSQL323 is deprecated. It is removed in MySQL 8.0.

• MYSQL40

  Equivalent toMYSQL40,HIGH_NOT_PRECEDENCE. This meansHIGH_NOT_PRECEDENCE plus some behaviors specific toMYSQL40. These are the same as forMYSQL323, except thatSHOW CREATE TABLE does not displayHEAP as the storage engine forMEMORY tables.

  Note

  As of MySQL 5.7.22,MYSQL40 is deprecated. It is removed in MySQL 8.0.

• ORACLE

  Equivalent toPIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER.

  Note

  As of MySQL 5.7.22,ORACLE is deprecated. It is removed in MySQL 8.0.

• POSTGRESQL

  Equivalent toPIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS.

  Note

  As of MySQL 5.7.22,POSTGRESQL is deprecated. It is removed in MySQL 8.0.

• TRADITIONAL

  Before MySQL 5.7.4, and in MySQL 5.7.8 and later,TRADITIONAL is equivalent toSTRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER, andNO_ENGINE_SUBSTITUTION.

  From MySQL 5.7.4 though 5.7.7,TRADITIONAL is equivalent toSTRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_AUTO_CREATE_USER, andNO_ENGINE_SUBSTITUTION. TheNO_ZERO_IN_DATE,NO_ZERO_DATE, andERROR_FOR_DIVISION_BY_ZERO modes are not named because in those versions their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES orSTRICT_TRANS_TABLES). Thus, the effects ofTRADITIONAL are the same in all MySQL 5.7 versions (and the same as in MySQL 5.6). For additional discussion, seeSQL Mode Changes in MySQL 5.7.

• For transactional tables, an error occurs for invalid or missing values in a data-change statement when eitherSTRICT_ALL_TABLES orSTRICT_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:

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

  • ForSTRICT_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 inSection 11.6, “Data Type Default Values”.

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

  For data-change operations (INSERT,UPDATE):

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

  • If strict mode is enabled, division by zero produces an error, unlessIGNORE is given as well. ForINSERT IGNORE andUPDATE IGNORE, division by zero insertsNULL and produces a warning.

  ForSELECT, division by zero returnsNULL. 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, unlessIGNORE is given as well. ForINSERT IGNORE andUPDATE 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, unlessIGNORE is given as well. ForINSERT IGNORE andUPDATE IGNORE, dates with zero parts are inserted as'0000-00-00' (which is considered valid withIGNORE) and produce a warning.

• The Effect of IGNORE on Statement Execution

• The Effect of Strict SQL Mode on Statement Execution

• CREATE TABLE ... SELECT:IGNORE does not apply to theCREATE TABLE orSELECT parts of the statement but to inserts into the table of rows produced by theSELECT. 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: WithIGNORE, 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: WithIGNORE, rows that duplicate an existing row on a unique key value are discarded.

• UPDATE: WithIGNORE, 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.

• 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

• ALTER TABLE

• CREATE TABLE

• CREATE TABLE ... SELECT

• DELETE (both single table and multiple table)

• INSERT

• LOAD DATA

• LOAD XML

• SELECT SLEEP()

• UPDATE (both single table and multiple table)

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

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

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

• ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE, andNO_ZERO_IN_DATE are not part of strict SQL mode, but it is intended that they be used together with strict mode. As a reminder, a warning occurs if they are enabled without also enabling strict mode or vice versa.

• ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE, andNO_ZERO_IN_DATE are enabled by default.