MySQL Reference Manual :: 5.3.2 The Server SQL Mode

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. Note: 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. (New in MySQL 5.0.2)

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 is 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 or UPDATE, or in any expression (for example, in a select list or WHERE clause) that involves table data and a division by zero. If this mode is not given, 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. (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_ENGINE_SUBSTITUTION

Prevents automatic substitution of storage engine when the requested storage engine is disabled or not compiled in.

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. See Section 12.8, “Cast Functions and Operators”. (New in MySQL 4.0.2)

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. (New 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, we insert a '0000-00-00' date for any such date. When not in strict mode, the date is accepted but a warning is generated. (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)

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 Section 13.1.5, “CREATE TABLE Syntax”.

ANSI

Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE. Before MySQL 4.1.11 and 5.0.3, ANSI also includes ONLY_FULL_GROUP_BY. See Section 1.7.3, “Running MySQL in 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.