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

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

  BecauseERROR_FOR_DIVISION_BY_ZERO is deprecated, you should expect it to 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 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 11.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 11.2.5, “Function Name Parsing and Resolution”.

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

  BecauseNO_ZERO_DATE is deprecated, you should expect it to 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

  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.

  BecauseNO_ZERO_IN_DATE is deprecated, you should expect it to 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, 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.

  A MySQL extension to standard SQL permits references in theHAVING clause to aliased expressions in the select list. TheHAVING clause can refer to aliases regardless of whetherONLY_FULL_GROUP_BY is enabled.

  For additional discussion and examples, seeSection 14.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.

  Note

  As of MySQL 8.0.13,PAD_CHAR_TO_FULL_LENGTH is deprecated. Expect it to be removed in a future version of MySQL.

  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.

• STRICT_TRANS_TABLES

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

• TIME_TRUNCATE_FRACTIONAL

  Control whether rounding or truncation occurs when inserting aTIME,DATE, orTIMESTAMP value with a fractional seconds part into a column having the same type but fewer fractional digits. The default behavior is to use rounding. If this mode is enabled, truncation occurs instead. The following sequence of statements illustrates the difference:

  CREATE TABLE t (id INT, tval TIME(1));SET sql_mode='';INSERT INTO t (id, tval) VALUES(1, 1.55);SET sql_mode='TIME_TRUNCATE_FRACTIONAL';INSERT INTO t (id, tval) VALUES(2, 1.55);

  The resulting table contents look like this, where the first value has been subject to rounding and the second to truncation:

  mysql> SELECT id, tval FROM t ORDER BY id;+------+------------+| id   | tval       |+------+------------+|    1 | 00:00:01.6 ||    2 | 00:00:01.5 |+------+------------+

  See alsoSection 13.2.6, “Fractional Seconds in Time Values”.

• ANSI

  Equivalent toREAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE, andONLY_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”.

• TRADITIONAL

  TRADITIONAL is equivalent toSTRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, andNO_ENGINE_SUBSTITUTION.

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