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

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 is empty (no modes set).

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

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.

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.

Full List of 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
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.

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.

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.

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:

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.

Prevent the GRANT statement
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
using IDENTIFIED WITH.

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.

By default, subtraction between integer operands produces an
UNSIGNED result if any operand
isUNSIGNED. 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:

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.

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.

Reject queries for which the select list or
HAVING condition refer to nonaggregated
columns that are not named in the GROUP BY
clause.

The following queries are invalid with
ONLY_FULL_GROUP_BY enabled.
The first is invalid because the nonaggregated
address column in the select list is not
named in the GROUP BY clause, and the
second because max_age in the
HAVING clause is not named in the
GROUP BY clause:

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): 't.address' isn't in GROUP BY

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

If a query has aggregate functions and no GROUP
BY clause, it cannot have nonaggregated columns in
the select list or ORDER BY list with
ONLY_FULL_GROUP_BY enabled:

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

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.

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

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

Combination SQL Modes

ANSI mode also causes the
server to return an error for queries where a set function
S with an outer reference
S(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 the
WHERE clause of that query. Standard SQL
requires an error in this situation. If
ANSI mode is not enabled,
the server treats
S(outer_ref)
in such queries the same way that it would interpret
S(const).

User Comments

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.