Skip navigation links
5.1.8 Server-Side Help »
Section Navigation      [Toggle]

5.1.7. Server SQL Modes

The MySQL server can operate in different SQL modes, and (as of MySQL 4.1) can apply these modes differentially 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, or by using sql-mode="modes" in my.cnf (Unix operating systems) or my.ini (Windows). 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="" on the command line, or sql-mode="" in my.cnf on Unix systems or in my.ini on Windows) if you want to clear it explicitly.

Beginning with MySQL 4.1, 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;

This mode changes syntax and behavior to conform more closely to standard SQL, and is available beginning in MySQL 4.1.1.

The following list describes all supported modes:

  • 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 literal strings, because it is interpreted as an identifier. (Added in MySQL 4.0.0)

  • IGNORE_SPACE

    Allow 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 8.2, “Database, Table, Index, Column, and Alias 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. It is always allowable to have spaces after a UDF name, regardless of whether IGNORE_SPACE is enabled.

    For further discussion of IGNORE_SPACE, see Section 8.2.3, “Function Name Parsing and Resolution”.

    (Added in MySQL 4.0.0)

  • 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. (Added in MySQL 4.1.1)

    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. As of MySQL 4.1.1, mysqldump automatically includes a statement in the dump output that enables NO_AUTO_VALUE_ON_ZERO to avoid this problem.

  • NO_DIR_IN_CREATE

    When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers. (Added in MySQL 4.0.15)

  • 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. (Added in MySQL 4.1.1)

  • 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. (Added in MySQL 4.1.1)

  • 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. (Added in MySQL 4.1.1)

  • NO_UNSIGNED_SUBTRACTION

    In integer subtraction operations, do not mark the result as UNSIGNED if one of the operands is unsigned. In other words, the result of a subtraction is always signed whenever this mode is in effect, even if one of the operands 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 |      |     | 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) |      |     | 0       |       |
+-------+------------+------+-----+---------+-------+

    Note that this means that BIGINT UNSIGNED is not 100% usable in all contexts. See Section 11.9, “Cast Functions and Operators”. (Added in MySQL 4.0.2) 

    mysql> SET SQL_MODE = '';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+

mysql> SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+

  • ONLY_FULL_GROUP_BY

    Do not allow queries for which the SELECT list refers to nonaggregated columns that are not named in the GROUP BY clause. (Added in MySQL 4.0.0) The following query is invalid with this mode enabled because address is not named in the GROUP BY clause: 

    SELECT name, address, MAX(age) FROM t GROUP BY name;

  • PIPES_AS_CONCAT

    Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR. (Added in MySQL 4.0.0)

  • REAL_AS_FLOAT

    Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE. (Added in MySQL 4.0.0)

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.

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.