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.
The default SQL mode is empty (no modes set).
To set the SQL mode at server startup, use the
--sql-mode="
option on the command line, or
modes"sql-mode="
in an option file such as modes"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.
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;
The most important sql_mode
values 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 details are given later in this section. (Implemented in MySQL 5.0.2)
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.
When this manual refers to “strict mode,” it means a
mode with either or both
STRICT_TRANS_TABLES or
STRICT_ALL_TABLES enabled.
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
DATE and
DATETIME columns. It does not
apply TIMESTAMP columns, which
always require a valid date.
This mode is implemented in MySQL 5.0.2. Before 5.0.2, this
was the default MySQL date-handling mode. As of 5.0.2, 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
ERROR_FOR_DIVISION_BY_ZERO
mode affects handling of division by zero, which includes
MOD(.
For data-change operations
(N,0)INSERT,
UPDATE), its effect also
depends on whether strict SQL mode is enabled.
If this mode is not enabled, division by zero inserts
NULL and produces no warning.
If this mode is enabled, division by zero inserts
NULL and produces a warning.
If this mode and strict mode are enabled, division by zero
produces an error, unless IGNORE is
given as well. For INSERT IGNORE and
UPDATE IGNORE, division by zero inserts
NULL and produces a warning.
For SELECT, division by zero
returns NULL. Enabling
ERROR_FOR_DIVISION_BY_ZERO
causes a warning to be produced as well, regardless of whether
strict mode is enabled.
This mode was implemented in MySQL 5.0.2.
From MySQL 5.0.2 on, 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).
Before MySQL 5.0.2, the expression is parsed as (NOT
a) BETWEEN b AND c. The old higher-precedence
behavior can be obtained by enabling the
HIGH_NOT_PRECEDENCE SQL
mode. (Added in MySQL 5.0.2)
mysql>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:
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)
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.
For further discussion of
IGNORE_SPACE, see
Section 9.2.3, “Function Name Parsing and Resolution”.
Prevent the GRANT statement
from automatically creating new users if it would otherwise do
so, unless a nonempty password also is specified. (Added in
MySQL 5.0.2)
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.
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. (Implemented in MySQL
5.0.1)
When creating a table, ignore all INDEX
DIRECTORY and DATA DIRECTORY
directives. This option is useful on slave replication
servers.
Control automatic substitution of the default storage engine
when a statement such as CREATE
TABLE or ALTER TABLE
specifies a storage engine that is disabled or not compiled
in. (Implemented in MySQL 5.0.8)
With NO_ENGINE_SUBSTITUTION
disabled, 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.
With NO_ENGINE_SUBSTITUTION
enabled, an error occurs and the table is not created or
altered if the desired engine is unavailable for any reason
(whether disabled or invalid).
Do not print MySQL-specific column options in the output of
SHOW CREATE TABLE. This mode is
used by mysqldump in portability mode.
Do not print MySQL-specific index options in the output of
SHOW CREATE TABLE. This mode is
used by mysqldump in portability mode.
Do not print MySQL-specific table options (such as
ENGINE) in the output of
SHOW CREATE TABLE. This mode is
used by mysqldump in portability mode.
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:
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 | | | 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) | | | 0 | | +-------+------------+------+-----+---------+-------+
Note that this means that BIGINT UNSIGNED
is not 100% usable in all contexts. See
Section 12.10, “Cast Functions and Operators”.
mysql>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 | +-------------------------+
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. This mode
was added in MySQL 5.0.2.
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. This mode
was added in MySQL 5.0.2.
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 (as of MySQL
5.0.23) HAVING list 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
For additional discussion, see Section 12.16.3, “MySQL Handling of GROUP BY”.
Treat || as a
string concatenation operator (same as
CONCAT()) rather than as a
synonym for OR.
Treat REAL as a synonym for
FLOAT. By default, MySQL treats
REAL as a synonym for
DOUBLE.
Enable strict mode for all storage engines. Invalid data values are rejected. Additional details follow. (Added in MySQL 5.0.2)
Enable strict mode for transactional storage engines, and when possible for nontransactional storage engines. Additional details follow. (Implemented in MySQL 5.0.2)
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.)
If strict mode is not in effect, MySQL inserts adjusted values for
invalid or missing values and produces warnings (see
Section 13.7.5.37, “SHOW WARNINGS Syntax”). In strict mode, you can produce
this behavior by using
INSERT IGNORE or
UPDATE IGNORE.
For statements such as SELECT that
do not change data, invalid values generate a warning in strict
mode, not an error.
Strict mode does not affect whether foreign key constraints are
checked. foreign_key_checks can
be used for that. (See Section 5.1.4, “Server System Variables”.)
Strict SQL mode is in effect if either
STRICT_ALL_TABLES or
STRICT_TRANS_TABLES is enabled,
although the effects of these modes differ somewhat:
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”.
Strict mode also affects handling of division by zero, zero dates,
and zeros in dates, in conjunction with the
ERROR_FOR_DIVISION_BY_ZERO,
NO_ZERO_DATE, and
NO_ZERO_IN_DATE modes. For
details, see the descriptions of those modes.
The following special modes are provided as shorthand for
combinations of mode values from the preceding list. All are
available in MySQL 5.0 beginning with version 5.0.0,
except for TRADITIONAL, which
was implemented in MySQL 5.0.2.
Equivalent to REAL_AS_FLOAT,
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE. Before MySQL
5.0.3, ANSI also includes
ONLY_FULL_GROUP_BY.
As of MySQL 5.0.40, 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:
S(outer_ref)
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
in such queries the same way that it would interpret
S(outer_ref),
as was always done prior to 5.0.40.
S(const)
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS.
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS,
NO_AUTO_CREATE_USER.
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS.
Equivalent to
NO_FIELD_OPTIONS,
HIGH_NOT_PRECEDENCE.
Equivalent to
NO_FIELD_OPTIONS,
HIGH_NOT_PRECEDENCE.
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS,
NO_AUTO_CREATE_USER.
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS.
Equivalent to
STRICT_TRANS_TABLES,
STRICT_ALL_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER.

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.
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html