5.1.7. Server SQL Modes
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.0 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 usingmodes
"sql-mode="
in modes
"my.cnf
(Unix operating systems) or my.ini
(Windows). modes
is a list of different modes separated by comma (“,
”) characters. The default value is empty (no modes set). The modes
value also can be empty (--sql-mode=""
on the command line, or sql-mode=""
in my.cnf
on Unix systems or in my.ini
on 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 modes
'sql_mode
system value. 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. Any client can change its own session sql_mode
value at any time.
You can retrieve the current global or session sql_mode
value with 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 non-transactional 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. (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 where at least one ofSTRICT_TRANS_TABLES
or STRICT_ALL_TABLES
is enabled.
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
DATE
andDATETIME
columns. It does not applyTIMESTAMP
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 allow such dates, enableALLOW_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. WithANSI_QUOTES
enabled, you cannot use double quotes 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 anINSERT
orUPDATE
. If this mode is not enabled, MySQL instead returnsNULL
for divisions by zero. ForINSERT IGNORE
orUPDATE IGNORE
, MySQL generates a warning for divisions by zero, but the result of the operation isNULL
. (Implemented in MySQL 5.0.2) -
From MySQL 5.0.2 on, the precedence of the
NOT
operator is such that expressions such asNOT a BETWEEN b AND c
are parsed asNOT (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 theHIGH_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 -
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 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 syntaxThe 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 allowable to have spaces after a UDF or stored function name, regardless of whetherIGNORE_SPACE
is enabled.For further discussion of
IGNORE_SPACE
, see Section 8.2.3, “Function Name Parsing and Resolution”. -
Prevent the
GRANT
statement from automatically creating new users if it would otherwise do so, unless a non-empty password also is specified. (Added in MySQL 5.0.2) -
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 if
0
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 with mysqldump 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. mysqldump now automatically includes in its output a statement that enablesNO_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
andDATA 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