(mysql.info) server-sql-mode
Info Catalog
(mysql.info) server-status-variables
(mysql.info) mysqld
(mysql.info) server-shutdown
5.2.5 The Server SQL Mode
-------------------------
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.
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. 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="") if you want to
clear it explicitly.
You can change the SQL mode at runtime by using a `SET [GLOBAL|SESSION]
sql_mode='MODES'' statement to set the `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:
* `ANSI'
Change syntax and behavior to be more conformant to standard SQL.
* `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. (Implemented 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' aborts 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 are
not be rolled back, resulting in a `partially done' update. (Added
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 supported modes:
* `ALLOW_INVALID_DATES'
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' 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 allow such dates,
enable `ALLOW_INVALID_DATES'.
* `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 with this mode
enabled. With `ANSI_QUOTES' enabled, you cannot use double quotes
to quote a literal string, because it is interpreted as an
identifier.
* `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' or
`UPDATE'. If this mode is not enabled, MySQL instead returns
`NULL' for divisions by zero. If used in `INSERT IGNORE' or `UPDATE
IGNORE', MySQL generates a warning for divisions by zero, but the
result of the operation is `NULL'. (Implemented in MySQL 5.0.2)
* `HIGH_NOT_PRECEDENCE'
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 = '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";
The `IGNORE_SPACE' SQL mode applies to built-in functions, not to
stored routines. it is always allowable to have spaces after a
routine name, regardless of whether `IGNORE_SPACE' is enabled.
* `NO_AUTO_CREATE_USER'
Prevent `GRANT' 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'
`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.
* `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. (Implemented 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.
* `NO_ENGINE_SUBSTITUTION'
Prevents automatic substitution of the default storage engine when
a statement such as `CREATE TABLE' specifies a storage engine that
is disabled or not compiled in. (Implemented in MySQL 5.0.8)
* `NO_FIELD_OPTIONS'
Do not print MySQL-specific column options in the output of `SHOW
CREATE TABLE'. This mode is used by `mysqldump' in portability
mode.
* `NO_KEY_OPTIONS'
Do not print MySQL-specific index options in the output of `SHOW
CREATE TABLE'. This mode is used by `mysqldump' in portability
mode.
* `NO_TABLE_OPTIONS'
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.
* `NO_UNSIGNED_SUBTRACTION'
In subtraction operations, do not mark the result as `UNSIGNED' if
one of the operands is unsigned. Note that this makes `BIGINT
UNSIGNED' not 100% usable in all contexts. See
cast-functions.
* `NO_ZERO_DATE'
In strict mode, don't allow `'0000-00-00'' as a valid date. You can
still insert zero dates with the `IGNORE' option. When not in
strict mode, the date is accepted but a warning is generated.
(Added in MySQL 5.0.2)
* `NO_ZERO_IN_DATE'
In strict mode, don't accept dates where the month or day part is
0. If used with the `IGNORE' option, MySQL inserts a `'0000-00-00''
date for any such date. When not in strict mode, the date is
accepted but a warning is generated. (Added in MySQL 5.0.2)
* `ONLY_FULL_GROUP_BY'
Do not allow queries for which the `GROUP BY' clause refers to a
column that is not present in the output column list.
* `PIPES_AS_CONCAT'
Treat `||' as a string concatenation operator (same as `CONCAT()')
rather than as a synonym for `OR'.
* `REAL_AS_FLOAT'
Treat `REAL' as a synonym for `FLOAT'. By default, MySQL treats
`REAL' as a synonym for `DOUBLE'.
* `STRICT_ALL_TABLES'
Enable strict mode for all storage engines. Invalid data values
are rejected. Additional detail follows. (Added in MySQL 5.0.2)
* `STRICT_TRANS_TABLES'
Enable strict mode for transactional storage engines, and when
possible for non-transactional storage engines. Additional
details follow. (Implemented in MySQL 5.0.2)
Strict mode controls how MySQL handles input 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 still
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 data-type-defaults.
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'. See show-warnings.
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.
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'. Before MySQL 5.0.3, `ANSI' also includes
`ONLY_FULL_GROUP_BY'. See 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) server-status-variables
(mysql.info) mysqld
(mysql.info) server-shutdown
automatically generated byinfo2html