MySQL - SQL Modes

Many operational characteristics of MySQL server can be configured by setting SQL mode. This mode consists of optional values that each control some aspect of query processing. By setting the SQL mode appropriately, a client program can instruct the server how strict or forgiving to be about accepting input data, enable or disable behaviors relating to standard SQL conformance, or provide better compatibility with other database systems.

SQL mode is controlled by means of the sql_mode system variable. To assign a value to this variable, use a SET statement. The value should be an empty string, or one or more mode names separated by commas. If the value is empty or contains more than one mode, it must be quoted. If the value is a single mode, quoting is optional. SQL mode values are not case sensitive.

To clear SQL mode:

SET sql_mode = '';

To set the SQL mode using a single mode value:

SET sql_mode = ANSI_QUOTES;
SET sql_mode = 'TRADITIONAL';

To set SQL modes:

SET sql_mode = 'IGNORE_SPACE,ANSI_QUOTES';

To check the current sql_mode setting:

SELECT @@sql_mode;

Some SQL mode values are composite modes that actually enable a set of modes. To see which mode values a composite mode consist of, retrieve the value after setting it:

SET sql_mode = 'TRADITIONAL';
SELECT @@sql_mode\G

ANSI_QUOTES: This mode causes the double quote character ('"') to be interpreted as an identifier quoting character rather than as a string-quoting character.

IGNORE_SPACE: By default, functions must be written with no space between the function name and the following paranthesis. Enabling this mode causes the server to ignore spaces after function names. This allows spaces to appear between the name and the parathensis, but also cause function names to be reserved words.

ERROR_FOR_DIVISION_BY_ZERO: By default, division by zero produces a result of NULL and is not treated specially. Enabling this mode causes division by zero in the context of inserting data into tables to produce a warning, or an error in strict mode.

STRICT_TRANS_TABLES, STRICT_ALL_TABLES: These values enable "strict mode", which imposes certain restrictions on what values are acceptable as database input. By default, MySQL is forgiving about accepting values that are missing, out of range, or malformed. Enabling strict mode causes bad values to be treated as erroneous. STRICT_TRANS_TABLES enables strict mode for transactional tables, and STRICT_ALL_TABLES enable strict mode for all tables.

TRADITIONAL: This is a composite mode that enables both strict modes plus several additional restrictions on acceptance of input data.

ANSI: This is a composite mode that causes MySQL Server to be more "ANSI-like". That is, it enables behaviors that is more like standard SQL, such as ANSI_QUOTES and PIPES_AS_CONCAT, which causes || to be treated as the string concatenation operator rather than as logical OR.

Section 5.8, "Handling Missing or Invalid Data Values", provides additional details about the use of strict and traditional SQL modes for controlling how restrictive the server is about accepting input data.

MySQL Reference Manual lists all available SQL modes.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License