The MySQL server can operate in different SQL modes, and can apply
these modes differently for different clients, depending on the
value of the
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 B.3, “MySQL 5.5 FAQ: Server SQL Mode”.
When working with
InnoDB tables, consider also
variable. It enables additional error checks for
The default SQL mode is empty (no modes set).
To set the SQL mode at server startup, use the
option on the command line, or
in an option file such as
operating systems) or
modes is a list of different modes
separated by commas. To clear the SQL mode explicitly, set it to
an empty string using
on the command line, or
in an option file.
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.
SET GLOBAL sql_mode = '
modes'; SET SESSION sql_mode = '
GLOBAL variable requires the
SUPER privilege and affects the
operation of all clients that connect from that time on. Setting
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
SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;
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.
The most important
values are probably these:
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.
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.
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.
The following list describes all supported SQL modes:
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
DATETIME columns. It does not
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
"” 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.
For data-change operations
produces an error in strict mode (otherwise a warning) for
division by zero, which includes
If this mode is not enabled, MySQL instead returns
NULL for division by zero. For
IGNORE, MySQL generates a warning for division by
zero, but the result of the operation is
The precedence of the
operator is such that expressions such as
BETWEEN b AND c are parsed as
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
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 9.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:
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_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
Section 9.2.4, “Function Name Parsing and Resolution”.
from automatically creating new users 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
affects handling of
Normally, you generate the next sequence number for the column
by inserting either
0 into it.
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
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
reloading the dump file solves this problem.
mysqldump now automatically includes in its
output a statement that enables
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
directives. This option is useful on slave replication
Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:
CREATE TABLE the
default engine is used and a warning occurs if the desired
engine is unavailable. For
TABLE, a warning occurs and the table is not
enabled, an error occurs and the table is not created or
altered if the desired engine is unavailable.
By default, subtraction between integer operands produces an
UNSIGNED result if any operand
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
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
is not 100% usable in all contexts. See
Section 12.10, “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,
'0000-00-00' is not
permitted as a valid date. You can still insert zero dates
IGNORE option. When not in strict
mode, the date is accepted but a warning is generated.
In strict mode, dates are not permitted in which 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
IGNORE option, MySQL inserts a
'0000-00-00' date for any such date. When
not in strict mode, the date is accepted but a warning is
Do not permit queries for which the select list or or
HAVING list refers to nonaggregated columns
that are not named in the
GROUP BY clause.
The following queries are invalid with
The first is invalid because
address in the
select list is not named in the
clause, and the second because
HAVING clause is not named in the
GROUP BY clause:
SELECT name, address, MAX(age) FROM t GROUP BY name;ERROR 1055 (42000): 't.address' isn't in GROUP BY
SELECT name, MAX(age) AS max_age FROM t GROUP BY name->
HAVING max_age < 30;Empty set (0.00 sec) ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause
In the second example, the query could be rewritten to use
HAVING MAX(age) instead, so that the
reference is to a column named in an aggregate function.
max_age fails because it
is an aggregate function.)
In addition, if a query has aggregate functions and no
GROUP BY clause, it cannot have
nonaggregated columns in the select list or
SELECT name, MAX(age) FROM t;ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
For more information, see
Section 12.16.3, “MySQL Extensions to
By default, trailing spaces are trimmed from
CHAR column values on
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.
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 details follow.
Enable strict mode for transactional storage engines, and when possible for nontransactional storage engines. Additional details follow.
Strict mode controls how MySQL handles invalid or missing values
in data-change statements such as
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
NULL column that has no explicit
DEFAULT clause in its definition. (For a
inserted if the value is missing.)
For statements such as
do not change data, invalid values generate a warning in strict
mode, not an error.
For transactional tables, an error occurs for invalid or missing
values in a data-change statement when either of the
STRICT_TRANS_TABLES modes 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:
returns an error and ignores the rest of the rows, but the
earlier rows have been inserted or updated. This means that
you might get a partial update, which might not be what you
want. To avoid this, use single-row statements, which can be
aborted without changing the table.
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 11.5, “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
“zero” dates. To disallow these as well, enable the
NO_ZERO_DATE SQL modes in
addition to strict mode.
If you are not using strict mode (that is, neither
STRICT_ALL_TABLES is enabled),
MySQL inserts adjusted values for invalid or missing values and
produces warnings. In strict mode, you can produce this behavior
The following special modes are provided as shorthand for combinations of mode values from the preceding list.
ANSI mode also causes the
server to return an error for queries where a set function
S with 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 ...);
aggregated in the outer query because it appears in the
WHERE clause of that query. Standard SQL
requires an error in this situation. If
ANSI mode is not enabled,
the server treats
in such queries the same way that it would interpret
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices