MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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.
For answers to questions often asked about server SQL modes in MySQL, see Section A.3, “MySQL 5.6 FAQ: Server SQL Mode”.
When working with InnoDB
tables, consider also
the innodb_strict_mode
system
variable. It enables additional error checks for
InnoDB
tables.
The default SQL mode is
NO_ENGINE_SUBSTITUTION
.
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. For example,
mysql_install_db creates a default option
file named my.cnf
in the base
installation directory. This file contains a line that sets
the SQL mode; see Section 4.4.3, “mysql_install_db — Initialize MySQL Data Directory”.
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
setting, select its
value:
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 the source and replica can also lead to problems. For best results, you should always use the same server SQL mode on the source and replica.
For more information, see Section 19.6, “Restrictions and Limitations on Partitioning”.
The most important sql_mode
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.
With TRADITIONAL
mode
enabled, an INSERT
or
UPDATE
aborts as soon as an
error occurs. If you are using a nontransactional storage
engine, this may not be what you want because data changes
made prior to the error may not be rolled back, resulting
in a “partially done” update.
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 may be useful for Web applications
that obtain year, month, and day in three different fields
and 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.
With ALLOW_INVALID_DATES
disabled, 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 they are interpreted as identifiers.
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.
As of MySQL 5.6.17,
ERROR_FOR_DIVISION_BY_ZERO
is deprecated and setting the sql_mode
value to include it generates a warning.
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.
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.5, “Function Name Parsing and Resolution”.
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. For this
reason, mysqldump automatically includes
in its output a statement that enables
NO_AUTO_VALUE_ON_ZERO
.
Enabling this mode disables the use of the backslash
character (\
) as an escape character
within strings and identifiers. With this mode enabled,
backslash becomes an ordinary character like any other, and
the default escape sequence for
LIKE
expressions is changed so
that no escape character is used.
When creating a table, ignore all INDEX
DIRECTORY
and DATA DIRECTORY
directives. This option is useful on replica 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.
Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:
With
NO_ENGINE_SUBSTITUTION
disabled, for CREATE TABLE
the 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.
With
NO_ENGINE_SUBSTITUTION
enabled, an error occurs and the table is not created or
altered if the desired engine is unavailable.
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.
Subtraction between integer values, where one is of type
UNSIGNED
, produces an unsigned result by
default. If the result would otherwise have been negative,
an error results:
mysql>SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec) mysql>SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
If the
NO_UNSIGNED_SUBTRACTION
SQL mode is enabled, the result is negative:
mysql>SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql>SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+
If the result of such an operation is used to update an
UNSIGNED
integer column, the result is
clipped to the maximum value for the column type, or clipped
to 0 if
NO_UNSIGNED_SUBTRACTION
is
enabled. With strict SQL mode enabled, an error occurs and
the column remains unchanged.
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 | NO | | 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) | NO | | 0 | | +-------+------------+------+-----+---------+-------+
This means that BIGINT UNSIGNED
is not
100% usable in all contexts. See
Section 12.11, “Cast Functions and Operators”.
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.
As of MySQL 5.6.17,
NO_ZERO_DATE
is deprecated
and setting the sql_mode
value to include
it generates 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.
As of MySQL 5.6.17,
NO_ZERO_IN_DATE
is
deprecated and setting the sql_mode
value
to include it generates a warning.
Reject queries for which the select list,
HAVING
condition, or ORDER
BY
list refer to nonaggregated columns that are
not named in the GROUP BY
clause.
A MySQL extension to standard SQL permits references in the
HAVING
clause to aliased expressions in
the select list. Enabling
ONLY_FULL_GROUP_BY
disables this extension, thus requiring the
HAVING
clause to be written using
unaliased expressions.
For additional discussion and examples, see Section 12.19.3, “MySQL Handling of GROUP BY”.
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.
mysql>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)
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 SQL mode for all storage engines. Invalid data values are rejected. For details, see Strict SQL Mode.
Enable strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines. For details, see Strict SQL Mode.
The following special modes are provided as shorthand for combinations of mode values from the preceding list.
Equivalent to
REAL_AS_FLOAT
,
PIPES_AS_CONCAT
,
ANSI_QUOTES
,
IGNORE_SPACE
.
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
)
.
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 MYSQL323
,
HIGH_NOT_PRECEDENCE
. This
means HIGH_NOT_PRECEDENCE
plus some SHOW CREATE TABLE
behaviors specific to
MYSQL323
:
TIMESTAMP
column display
does not include DEFAULT
or
ON UPDATE
attributes.
String column display does not include character set and
collation attributes. For
CHAR
and
VARCHAR
columns, if the
collation is binary, BINARY
is
appended to the column type.
The
ENGINE=
table option displays as
engine_name
TYPE=
.
engine_name
For MEMORY
tables, the
storage engine is displayed as HEAP
.
Equivalent to MYSQL40
,
HIGH_NOT_PRECEDENCE
. This
means HIGH_NOT_PRECEDENCE
plus some behaviors specific to
MYSQL40
. These are the
same as for MYSQL323
,
except that SHOW CREATE TABLE
does not display HEAP
as the storage
engine for MEMORY
tables.
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
, and
NO_ENGINE_SUBSTITUTION
.
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.) Strict mode also affects DDL
statements such as CREATE TABLE
.
If strict mode is not in effect, MySQL inserts adjusted values
for invalid or missing values and produces warnings (see
Section 13.7.5.41, “SHOW WARNINGS Statement”). 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.
As of MySQL 5.6.11, strict mode produces an error for attempts to create a key that exceeds the maximum key length. Previously, this resulted in a warning and truncation of the key to the maximum key length (the same as when strict mode is not enabled).
Strict mode does not affect whether foreign key constraints are
checked. foreign_key_checks
can
be used for that. (See
Section 5.1.7, “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.5, “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.