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

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.

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

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. If strict SQL mode is 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:

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.7.4,
NO_ZERO_DATE is
deprecated. In MySQL 5.7.4 through 5.7.7,
NO_ZERO_DATE does nothing
when named explicitly. Instead, its effect is included in
the effects of strict SQL mode. In MySQL 5.7.8 and later,
NO_ZERO_DATE does have an
effect when named explicitly and is not part of strict mode,
as before MySQL 5.7.4. However, it should be used in
conjunction with strict mode and is enabled by default. A
warning occurs if
NO_ZERO_DATE is enabled
without also enabling strict mode or vice versa. For
additional discussion, see
SQL Mode Changes in MySQL 5.7.

Because NO_ZERO_DATE is
deprecated, it will be removed in a future MySQL release as
a separate mode name and its effect included in the effects
of strict SQL mode.

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.7.4,
NO_ZERO_IN_DATE is
deprecated. In MySQL 5.7.4 through 5.7.7,
NO_ZERO_IN_DATE does
nothing when named explicitly. Instead, its effect is
included in the effects of strict SQL mode. In MySQL 5.7.8
and later, NO_ZERO_IN_DATE
does have an effect when named explicitly and is not part of
strict mode, as before MySQL 5.7.4. However, it should be
used in conjunction with strict mode and is enabled by
default. A warning occurs if
NO_ZERO_IN_DATE is enabled
without also enabling strict mode or vice versa. For
additional discussion, see
SQL Mode Changes in MySQL 5.7.

Because NO_ZERO_IN_DATE is
deprecated, it will be removed in a future MySQL release as
a separate mode name and its effect included in the effects
of strict SQL mode.

Reject queries for which the select list,
HAVING condition, or ORDER
BY list refer to nonaggregated columns that are
neither named in the GROUP BY clause nor
are functionally dependent on (uniquely determined by)
GROUP BY columns.

A MySQL extension to standard SQL permits references in the
HAVING clause to aliased expressions in
the select list. Before MySQL 5.7.5, enabling
ONLY_FULL_GROUP_BY
disables this extension, thus requiring the
HAVING clause to be written using
unaliased expressions. As of MySQL 5.7.5, this restriction
is lifted so that the HAVING clause can
refer to aliases regardless of whether
ONLY_FULL_GROUP_BY is
enabled.

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.

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

TIMESTAMP column display
does not include DEFAULT or
ON UPDATE attributes that were
introduced in MySQL 4.1.

String column display does not include character set and
collation attributes that were introduced in MySQL 4.1.
For CHAR and
VARCHAR columns, if the
collation is binary, BINARY is
appended to the column type.

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.) Strict mode also affects DDL
statements such as CREATE TABLE.

For statements such as SELECT
that do not change data, invalid values generate a warning in
strict mode, not an error.

Strict mode produces an error for attempts to create a key that
exceeds the maximum key length. When strict mode is not enabled,
this results in a warning and truncation of the key to the
maximum key length.

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

Strict mode affects handling of division by zero, zero dates,
and zeros in dates as follows:

Strict mode affects handling of division by zero, which
includes
MOD(N,0):

If strict mode is not enabled,
'0000-00-00' is permitted and inserts
produce no warning.

If strict mode is 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.

Strict mode affects whether the server permits dates in
which the year part is nonzero but the month or day part is
0 (dates such as '2010-00-01' or
'2010-01-00'):

If strict mode is not enabled, dates with zero parts are
permitted and inserts produce no warning.

If strict mode is 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' (which is considered
valid with IGNORE) and produce a
warning.

Comparison of the IGNORE Keyword and Strict SQL Mode

This section compares the effect on statement execution of the
IGNORE keyword (which downgrades errors to
warnings) and strict SQL mode (which upgrades warnings to
errors). It describes which statements they affect, and which
errors they apply to.

The following table presents a summary comparison of statement
behavior when the default is to produce an error versus a
warning. An example of when the default is to produce an error
is inserting a NULL into a NOT
NULL column. An example of when the default is to
produce a warning is inserting a value of the wrong data type
into a column (such as inserting the string
'abc' into an integer column).

Operational Mode

When Statement Default is Error

When Statement Default is Warning

Without IGNORE or strict SQL mode

Error

Warning

With IGNORE

Warning

Warning (same as without IGNORE or strict SQL mode)

With strict SQL mode

Error (same as without IGNORE or strict SQL mode)

Error

With IGNORE and strict SQL mode

Warning

Warning

One conclusion to draw from the table is that when the
IGNORE keyword and strict SQL mode are both
in effect, IGNORE takes precedence. This
means that, although IGNORE and strict SQL
mode can be considered to have opposite effects on error
handling, they do not cancel when used together.

The Effect of IGNORE on Statement Execution

Several statements in MySQL support an optional
IGNORE keyword. This keyword causes the
server to downgrade certain types of errors and generate
warnings instead. For a multiple-row statement,
IGNORE causes the statement to skip to the
next row instead of aborting.

For example, if the table t has a primary key
column i, attempting to insert the same value
of i into multiple rows normally produces a
duplicate-key error:

DELETE:
IGNORE causes MySQL to ignore errors
during the process of deleting rows.

INSERT: With
IGNORE, rows that duplicate an existing
row on a unique key value are discarded. Rows set to values
that would cause data conversion errors are set to the
closest valid values instead.

For partitioned tables where no partition matching a given
value is found, IGNORE causes the insert
operation to fail silently for rows containing the unmatched
value.

LOAD DATA,
LOAD XML: With
IGNORE, rows that duplicate an existing
row on a unique key value are discarded.

UPDATE: With
IGNORE, rows for which duplicate-key
conflicts occur on a unique key value are not updated. Rows
updated to values that would cause data conversion errors
are updated to the closest valid values instead.

The Effect of Strict SQL Mode on Statement Execution

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. In “strict” SQL mode, the server
upgrades certain warnings to errors.

For example, in non-strict SQL mode, inserting the string
'abc' into an integer column results in
conversion of the value to 0 and a warning:

Within stored programs, individual statements of the types just
listed execute in strict SQL mode if the program was defined
while strict mode was in effect.

Strict SQL mode applies to the following errors, represent a
class of errors in which an input value is either invalid or
missing. A value is invalid if it has the wrong data type for
the column or might be out of range. A value is missing if a new
row to be inserted does not contain a value for a NOT
NULL column that has no explicit
DEFAULT clause in its definition.

SQL Mode Changes in MySQL 5.7

In MySQL 5.7.5, the
ONLY_FULL_GROUP_BY SQL mode is
enabled by default because GROUP BY
processing has become more sophisticated to include detection of
functional dependencies. However, if you find that having
ONLY_FULL_GROUP_BY enabled
causes queries for existing applications to be rejected, either
of these actions should restore operation:

If it is possible to modify an offending query, do so,
either so that nonaggregated columns are functionally
dependent on GROUP BY columns, or by
referring to nonaggregated columns using
ANY_VALUE().

If it is not possible to modify an offending query (for
example, if it is generated by a third-party application),
set the sql_mode system variable at
server startup to not enable
ONLY_FULL_GROUP_BY.

The long term plan is still to have the three affected modes be
included in strict SQL mode and to remove them as explicit modes
in a future MySQL release. But to restore compatibility in MySQL
5.7 with MySQL 5.6 strict mode and to provide additional time
for affected applications to be modified, the following changes
were made in MySQL 5.7.8:

With the preceding changes, stricter data checking is still
enabled by default, but the individual modes can be disabled in
environments where it is currently desirable or necessary to do
so.

Although in MySQL 5.7.8 and later
ERROR_FOR_DIVISION_BY_ZERO,
NO_ZERO_DATE, and
NO_ZERO_IN_DATE can be used
separately from strict mode, it is intended that they be used
together. As a reminder, a warning occurs if they are enabled
without also enabling strict mode or vice versa.

Important

The following discussion applies only for MySQL versions 5.7.4
through 5.7.7. For upgrades from a version older than MySQL
5.7.4, we recommend upgrading to MySQL 5.7.8 or later, which
renders this discussion unnecessary.

The remainder of this section describes the SQL mode settings to
use in MySQL 5.7.4 through 5.7.7 to achieve the same statement
execution as before 5.7.4, including the cases for
INSERT and
UPDATE in which
IGNORE is given. It also provides guidelines
for determining whether applications need modification to behave
the same before and after the SQL mode changes.

The following table shows how to control handling of division by
zero for versions other than MySQL 5.7.4 through 5.7.7 and for
MySQL 5.7.4 through 5.7.7.

This discussion also describes how to prepare for an upgrade to
5.7.4 through 5.7.7 from a version older than 5.7.4.
Any modifications should be made before
upgrading.

There is no change in behavior between MySQL 5.6 and 5.7 for the
following SQL mode settings. A statement that executes under one
of these settings needs no modification to produce the same
result in 5.6 and 5.7:

Strict mode and the three deprecated modes are all not
enabled.

Strict mode and the three deprecated modes are all enabled.

A change from warnings in MySQL 5.6 to no warnings in MySQL 5.7
occurs for the following SQL mode settings. The result of
statement execution is the same in 5.6 and 5.7, so statements
need no modification unless warnings are considered significant:

A behavior change occurs under the following SQL mode settings.
A statement that executes under one of these settings must be
modified to produce the same result in 5.6 and 5.7:

Strict mode is not enabled,
NO_ZERO_IN_DATE is
enabled. For this mode setting, expect these differences in
statement execution:

In 5.6, the server inserts dates with zero parts as
'0000-00-00' and produces a warning.

In 5.7, the server inserts dates with zero parts as is
and produces no warning.

Strict mode is enabled, with some but not all of the three
deprecated modes enabled. For this mode setting, expect
these differences in statement execution:

Statements that would be affected by enabling the
not-enabled deprecated modes produce errors in 5.7 but not
in 5.6. Suppose that strict mode,
NO_ZERO_DATE, and
NO_ZERO_IN_DATE are
enabled, and a data-change statement performs division by
zero:

To prepare for an upgrade to MySQL 5.7.4 through 5.7.7, the main
principle is to make sure that your applications will operate
the same way in MySQL 5.6 and 5.7. For example, you can adopt
either of these approaches to application compatibility:

Modify the application to set the SQL mode on a
version-specific basis. If we assume that an application
will not be used with development versions of MySQL 5.7
prior to 5.7.4, it is possible to set the
sql_mode value for the
application based on the current server version as follows:

SET sql_mode = IF(LEFT(VERSION(),3)<'5.7',5.6 mode,5.7 mode);

The tables shown earlier in this section serve as a guide to
the appropriate equivalent modes for MySQL 5.6 and 5.7.

Modify the application to execute under an SQL mode for
which statements produce the same result in MySQL 5.6 and
5.7.

Tip

TRADITIONAL SQL mode in
MySQL 5.6 includes strict mode and the three deprecated
modes. If you write applications to operate in
TRADITIONAL mode in
MySQL 5.6, there is no change to make for MySQL 5.7.

Replication. You will encounter replication incompatibility
related to the SQL mode changes under the following
conditions:

MySQL 5.6 master and 5.7 slave

Statement-based replication

An SQL mode setting for which statements produce
different results in MySQL 5.6 and 5.7, as described
earlier

To handle this incompatibility, use one of these
workarounds:

Use row-based replication

Use IGNORE

Use an SQL mode for which statements do not produce
different results in MySQL 5.6 and 5.7

Stored programs (stored procedures and functions, triggers,
and events). Each stored program executes using the SQL mode
in effect at the time it was created. To identify stored
programs that may be affected by differences between MySQL
5.6 and 5.7 in SQL mode handling, use these queries:

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, SQL_MODE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SQL_MODE LIKE '%STRICT%'
OR SQL_MODE LIKE '%DIVISION%'
OR SQL_MODE LIKE '%NO_ZERO%';
SELECT TRIGGER_SCHEMA, TRIGGER_NAME, SQL_MODE
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE SQL_MODE LIKE '%STRICT%'
OR SQL_MODE LIKE '%DIVISION%'
OR SQL_MODE LIKE '%NO_ZERO%';
SELECT EVENT_SCHEMA, EVENT_NAME, SQL_MODE
FROM INFORMATION_SCHEMA.EVENTS
WHERE SQL_MODE LIKE '%STRICT%'
OR SQL_MODE LIKE '%DIVISION%'
OR SQL_MODE LIKE '%NO_ZERO%';

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.