The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients. This capability enables each application to tailor the server's operating mode to its own requirements.
For answers to some questions that are often asked about server SQL modes in MySQL, see Section A.3, “MySQL 5.1 FAQ: Server SQL Mode”.
Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.
You can set the default SQL mode by starting mysqld with the
--sql-mode="option, or by using
my.cnf(Unix operating systems) or
modesis a list of different modes separated by comma (“
,”) characters. The default value is empty (no modes set). The
modesvalue also can be empty (
--sql-mode=""on the command line, or
my.cnfon Unix systems or in
my.inion Windows) if you want to clear it explicitly.
You can change the SQL mode at runtime by using a
SET [GLOBAL|SESSION] sql_mode='statement to set the
sql_modesystem value. Setting the
GLOBALvariable requires the
SUPERprivilege and affects the operation of all clients that connect from that time on. Setting the
SESSIONvariable affects only the current client. Any client can change its own session
sql_modevalue at any time.
SQL mode and user-defined partitioning. Changing the server SQL mode after creating and inserting data into partitioned tables can cause major changes in the behavior of such tables, and could lead to loss or corruption of data. It is strongly recommended that you never change the SQL mode once you have created tables employing user-defined partitioning.
When replicating partitioned tables, differing SQL modes on master and slave can also lead to problems. For best results, you should always use the same server SQL mode on the master and on the slave.
See Section 18.5, “Restrictions and Limitations on Partitioning”, for more information.
You can retrieve the current global or session
sql_modevalue with the following statements:
SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;
The most important
sql_modevalues are probably these:
This mode changes syntax and behavior to conform more closely to standard SQL.
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 detail is given later in this section.
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.
The following list describes all supported modes:
Don't do 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
DATETIMEcolumns. It does not apply
TIMESTAMPcolumns, 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 allow such dates, enable
"” 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_QUOTESenabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.
Produce an error in strict mode (otherwise a warning) when a division by zero (or
MOD(X,0)) occurs during an
UPDATE. If this mode is not enabled, MySQL instead returns
NULLfor divisions by zero. For
UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is
The precedence of the
NOToperator is such that expressions such as
NOT a BETWEEN b AND care 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
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
Allow 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 8.2, “Schema Object Names”. For example, because there is a
COUNT()function, the use of
countas a table name in the following statement causes an error:
CREATE TABLE count (i INT);ERROR 1064 (42000): You have an error in your SQL syntax
The table name should be quoted:
CREATE TABLE `count` (i INT);Query OK, 0 rows affected (0.00 sec)
IGNORE_SPACESQL mode applies to built-in functions, not to user-defined functions or stored functions. It is always allowable to have spaces after a UDF or stored function name, regardless of whether
For further discussion of
IGNORE_SPACE, see Section 8.2.4, “Function Name Parsing and Resolution”.
GRANTstatement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified.
NO_AUTO_VALUE_ON_ZEROaffects handling of
AUTO_INCREMENTcolumns. Normally, you generate the next sequence number for the column by inserting either
NO_AUTO_VALUE_ON_ZEROsuppresses this behavior for
0so that only
NULLgenerates the next sequence number.
This mode can be useful if
0has been stored in a table's
0is 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
0values, resulting in a table with contents different from the one that was dumped. Enabling
NO_AUTO_VALUE_ON_ZERObefore 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.
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.
When creating a table, ignore all
DATA DIRECTORYdirectives. This option is useful on slave replication servers.
Up through MySQL 5.1.11, with
NO_ENGINE_SUBSTITUTIONdisabled, the default engine is used and a warning occurs if the desired engine is known but disabled or not compiled in. If the desired engine is invalid (not a known engine name), an error occurs and the table is not created or altered.
NO_ENGINE_SUBSTITUTIONenabled, an error occurs and the table is not created or altered if the desired engine is unavailable for any reason (whether disabled or invalid).
As of MySQL 5.1.12, storage engines can be pluggable at runtime, so the distinction between disabled and invalid no longer applies. All unavailable engines are treated the same way:
CREATE TABLEthe 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.
NO_ENGINE_SUBSTITUTIONenabled, an error occurs and the table is not created or altered if the desired engine is unavailable.
In integer subtraction operations, do not mark the result as
UNSIGNEDif one of the operands is unsigned. In other words, the result of a subtraction is always signed whenever this mode is in effect, even if one of the operands is unsigned. For example, compare the type of column
t1with that of column
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 | | | 0 | | +-------+---------------------+------+-----+---------+-------+ mysql>
CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;mysql>
DESCRIBE t2;+-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c2 | bigint(21) | | | 0 | | +-------+------------+------+-----+---------+-------+
Note that this means that
BIGINT UNSIGNEDis not 100% usable in all contexts. See Section 11.9, “Cast Functions and Operators”.
SET SQL_MODE = '';mysql>
SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | 18446744073709551615 | +-------------------------+ mysql>
SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';mysql>
SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+
In strict mode, don't allow
'0000-00-00'as a valid date. You can still insert zero dates with the
IGNOREoption. When not in strict mode, the date is accepted but a warning is generated.
In strict mode, do not accept dates where the year part is nonzero but the month or day part is 0 (for example,
'0000-00-00'is legal but
'2010-01-00'are not). If used with the
IGNOREoption, MySQL inserts a
'0000-00-00'date for any such date. When not in strict mode, the date is accepted but a warning is generated.
Do not allow queries for which the
SELECTlist refers to nonaggregated columns that are not named in the
GROUP BYclause. The following query is invalid with this mode enabled because
addressis not named in the
SELECT name, address, MAX(age) FROM t GROUP BY name;
As of MySQL 5.1.11, this mode also restricts references to nonaggregated columns in the
HAVINGclause that are not named in the
By default, trailing spaces are trimmed from
CHARcolumn values on retrieval. If
PAD_CHAR_TO_FULL_LENGTHis enabled, trimming does not occur and retrieved
CHARvalues are padded to their full length. This mode does not apply to
VARCHARcolumns, for which trailing spaces are retained on retrieval. This mode was added in MySQL 5.1.20.
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)
Enable strict mode for all storage engines. Invalid data values are rejected. Additional detail follows.
Enable strict mode for transactional storage engines, and when possible for nontransactional storage engines. Additional details follow.
Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-
NULLcolumn that has no explicit
DEFAULTclause in its definition. (For a
NULLis inserted if the value is missing.)
For transactional tables, an error occurs for invalid or missing values in a statement when either of the
STRICT_TRANS_TABLESmodes are enabled. The statement is aborted and rolled back.
For nontransactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:
STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, in this case, the earlier rows still have been inserted or updated. This means that you might get a partial update, which might not be what you want. To avoid this, it is best to use single-row statements because these can be aborted without changing the table.
STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and insert 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 10.1.4, “Data Type Default Values”.
Strict mode disallows invalid date values such as
'2004-04-31'. It does not disallow dates with zero month or day parts such as
'2004-04-00'or “zero” dates. To disallow these as well, enable the
NO_ZERO_DATESQL modes in addition to strict mode.
If you are not using strict mode (that is, neither
STRICT_ALL_TABLESis enabled), MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behavior by using
UPDATE IGNORE. See Section 184.108.40.206, “
The following special modes are provided as shorthand for combinations of mode values from the preceding list.
The descriptions include all mode values that are available in the most recent version of MySQL. For older versions, a combination mode does not include individual mode values that are not available except in newer versions.
As of MySQL 5.1.18,
ANSImode also causes the server to return an error for queries where a set function
Swith an outer reference
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 ...);
MAX(t1.b)cannot aggregated in the outer query because it appears in the
WHEREclause of that query. Standard SQL requires an error in this situation. If
ANSImode is not enabled, the server treats
in such queries the same way that it would interpret
, as was always done prior to 5.1.18.