(mysql.info.gz) Server SQL mode
Info Catalog
(mysql.info.gz) Server options
(mysql.info.gz) Configuring MySQL
(mysql.info.gz) Server system variables
5.2.2 The Server SQL Mode
-------------------------
The MySQL server can operate in different SQL modes, and (as of MySQL
4.1) can apply these modes differentially for different clients. This
allows an application to tailor server operation to its own
requirements.
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="MODES"' option. The value also can be empty
(`--sql-mode=""') if you want to reset it.
Beginning with MySQL 4.1, you can also change the SQL mode after
startup time by setting the `sql_mode' variable with a `SET
[SESSION|GLOBAL] sql_mode='MODES'' statement. 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
session `sql_mode' value.
MODES is a list of different modes separated by comma (`,') characters.
You can retrieve the current mode by issuing a `SELECT @@sql_mode'
statement. The default value is empty (no modes set).
The most important `sql_mode' values are probably these:
`ANSI'
Change syntax and behavior to be more conformant to standard SQL.
(New in MySQL 4.1.1)
`STRICT_TRANS_TABLES'
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. (New in MySQL 5.0.2)
`TRADITIONAL'
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. *
The `INSERT'/`UPDATE' will abort as soon as the error is noticed.
This may not be what you want if you are using a non-transactional
storage engine, because data changes made prior to the error will
not be rolled back, resulting in a "partially-done" update. (New
in MySQL 5.0.2)
When this manual refers to "strict mode," it means a mode where at least
one of `STRICT_TRANS_TABLES' or `STRICT_ALL_TABLES' is enabled.
The following list describes all the supported modes:
`ALLOW_INVALID_DATES'
Don't do full checking of dates in strict mode. 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 new in MySQL 5.0.2. Before 5.0.2, this was the
default MySQL date-handling mode. As of 5.0.2, enabling strict
mode causes the server to require that month and day values be
legal, not just in the range from 1 to 12 and 1 to 31. For
example, `'2004-04-31'' is legal with strict mode disabled, but
illegal with strict mode enabled. To allow such dates in strict
mode, enable `ALLOW_INVALID_DATES' as well.
`ANSI_QUOTES'
Treat `"' as an identifier quote character (like the ``' quote
character) and not as a string quote character. You can still use
``' to quote identifiers in ANSI mode. With `ANSI_QUOTES' enabled,
you cannot use double quotes to quote a literal string, because it
will be interpreted as an identifier. (New in MySQL 4.0.0)
`ERROR_FOR_DIVISION_BY_ZERO'
Produce an error in strict mode (otherwise a warning) when we
encounter a division by zero (or `MOD(X,0)') during an `INSERT'/
`UPDATE'. If this mode is not given, MySQL instead returns `NULL'
for divisions by zero. If used with `IGNORE', MySQL generates a
warning for divisions by zero, but the result of the operation is
`NULL'. (New in MySQL 5.0.2)
`HIGH_NOT_PRECEDENCE'
From MySQL 5.0.2 on, the `NOT' operator precedence is handled so
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. (New in MySQL 5.0.2)
mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
-> 0
mysql> SET sql_mode = 'broken_not';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
-> 1
`IGNORE_SPACE'
Allow spaces between a function name and the `(' character. This
forces all function names to be treated as reserved words. As a
result, if you want to access any database, table, or column name
that is a reserved word, you must quote it. For example, because
there is a `USER()' function, the name of the `user' table in the
`mysql' database and the `User' column in that table become
reserved, so you must quote them:
SELECT "User" FROM mysql."user";
(New in MySQL 4.0.0)
`NO_AUTO_CREATE_USER'
Prevent `GRANT' from automatically creating new users if it would
otherwise do so, unless a password also is specified. (New in
MySQL 5.0.2)
`NO_AUTO_VALUE_ON_ZERO'
`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. (New in MySQL
4.1.1)
This mode can be useful if `0' has been stored in a table's
`AUTO_INCREMENT' column. (This 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 different
contents than the one that was dumped. Enabling
`NO_AUTO_VALUE_ON_ZERO' before reloading the dump file solves this
problem. As of MySQL 4.1.1, `mysqldump' automatically includes a
statement in the dump output to enable `NO_AUTO_VALUE_ON_ZERO'.
`NO_BACKSLASH_ESCAPES'
Disable the use of the backslash character (`\') as an escape
character within strings. With this mode enabled, backslash
becomes any ordinary character like any other. (New in MySQL
5.0.1)
`NO_DIR_IN_CREATE'
When creating a table, ignore all `INDEX DIRECTORY' and `DATA
DIRECTORY' directives. This option is useful on slave replication
servers. (New in MySQL 4.0.15)
`NO_FIELD_OPTIONS'
Don't print MySQL-specific column options in the output of `SHOW
CREATE TABLE'. This mode is used by `mysqldump' in portability
mode. (New in MySQL 4.1.1)
`NO_KEY_OPTIONS'
Don't print MySQL-specific index options in the output of `SHOW
CREATE TABLE'. This mode is used by `mysqldump' in portability
mode. (New in MySQL 4.1.1)
`NO_TABLE_OPTIONS'
Don't print MySQL-specific table options (such as `ENGINE') in the
output of `SHOW CREATE TABLE'. This mode is used by `mysqldump' in
portability mode. (New in MySQL 4.1.1)
`NO_UNSIGNED_SUBTRACTION'
In subtraction operations, don't mark the result as `UNSIGNED' if
one of the operands is unsigned. Note that this makes `UNSIGNED
BIGINT' not 100% usable in all contexts. Cast Functions.
(New in MySQL 4.0.2)
`NO_ZERO_DATE'
Don't allow `'0000-00-00'' as a valid date. You can still insert
zero dates with the `IGNORE' option. (New in MySQL 5.0.2)
`NO_ZERO_IN_DATE'
Don't accept dates where the month or day part is 0. If used with
the `IGNORE' option, we insert a `'0000-00-00'' date for any such
date. (New in MySQL 5.0.2)
`ONLY_FULL_GROUP_BY'
Don't allow queries that in the `GROUP BY' part refer to a not
selected column. (New in MySQL 4.0.0)
`PIPES_AS_CONCAT'
Treat `||' as a string concatenation operator (same as `CONCAT()')
rather than as a synonym for `OR'. (New in MySQL 4.0.0)
`REAL_AS_FLOAT'
Treat `REAL' as a synonym for `FLOAT' rather than as a synonym for
`DOUBLE'. (New in MySQL 4.0.0)
`STRICT_ALL_TABLES'
Enable strict mode for all storage engines. Invalid data values
are rejected. Additional detail follows. (New in MySQL 5.0.2)
`STRICT_TRANS_TABLES'
Enable strict mode for transactional storage engines, and when
possible for non-transactional storage engines. Additional detail
follows. (New in MySQL 5.0.2)
Strict mode controls how MySQL handles values that are invalid or
missing. 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 column that has no explicit `DEFAULT' clause in
its definition.
For transactional tables, an error occurs for invalid or missing values
in a statement when either of the `STRICT_ALL_TABLES' or
`STRICT_TRANS_TABLES' modes are enabled. The statement is aborted and
rolled back.
For non-transactional 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 option is
enabled:
* For `STRICT_ALL_TABLES', MySQL returns an error and ignores the
rest of the rows. However, in this case, the earlier rows will
have been inserted or updated. This means that you might get a
partial update, which might not be what you want. To avoid this,
it's best to use single-row statements because these 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 insert 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 `CREATE
TABLE' CREATE TABLE.
Strict mode disallows invalid date values such as `'2004-04-31''. It
does not disallow dates with zero parts such as `2004-04-00'' or "zero"
dates. To disallow these as well, enable the `NO_ZERO_IN_DATE' and
`NO_ZERO_DATE' SQL modes in addition to strict mode.
If you are not using strict mode (that is, neither `STRICT_TRANS_TABLES'
nor `STRICT_ALL_TABLES' is enabled), MySQL inserts adjusted values for
invalid or missing values and produces warnings. In strict mode, you
can produce this behavior by using `INSERT IGNORE' or `UPDATE IGNORE'.
`SHOW WARNINGS' SHOW WARNINGS.
The following special modes are provided as shorthand for combinations
of mode values from the preceding list. All are available as of MySQL
4.1.1, except `TRADITIONAL' (5.0.2).
The descriptions include all mode values that are available in the most
recent version of MySQL. For older versions, a combination mode does
not include individual mode values that are not available except in
newer versions.
`ANSI'
Equivalent to `REAL_AS_FLOAT', `PIPES_AS_CONCAT', `ANSI_QUOTES',
`IGNORE_SPACE', `ONLY_FULL_GROUP_BY'. ANSI mode.
`DB2'
Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
`NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS'.
`MAXDB'
Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
`NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS',
`NO_AUTO_CREATE_USER'.
`MSSQL'
Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
`NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS'.
`MYSQL323'
Equivalent to `NO_FIELD_OPTIONS', `HIGH_NOT_PRECEDENCE'.
`MYSQL40'
Equivalent to `NO_FIELD_OPTIONS', `HIGH_NOT_PRECEDENCE'.
`ORACLE'
Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
`NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS',
`NO_AUTO_CREATE_USER'.
`POSTGRESQL'
Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
`NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS'.
`TRADITIONAL'
Equivalent to `STRICT_TRANS_TABLES', `STRICT_ALL_TABLES',
`NO_ZERO_IN_DATE', `NO_ZERO_DATE', `ERROR_FOR_DIVISION_BY_ZERO',
`NO_AUTO_CREATE_USER'.
Info Catalog
(mysql.info.gz) Server options
(mysql.info.gz) Configuring MySQL
(mysql.info.gz) Server system variables
automatically generated byinfo2html