The Server SQL Mode

In MySQL 4.0, a server SQL mode system variable named sql_mode was introduced to allow configuring certain aspects of how the server executes SQL statements. Initially, this variable could be set only by means of the --sql-mode startup option. As MySQL 4.1.1, the SQL mode also can be changed at runtime and individual clients can change the mode to affect their own connection. This means that any client can change how the server behaves in relation to itself without impact on other clients.

The SQL mode affects behaviors such as identifier quoting and handling of invalid values during data entry. The following list describes a few of the possible mode values:

• ANSI_QUOTES tells the server to recognize double quote as an identifier quoting character.

• PIPES_AS_CONCAT causes || to be treated as the standard SQL string concatenation operator rather than as a synonym for the OR operator.

• ANSI is a composite mode. It turns on ANSI_QUOTES, PIPES_AS_CONCAT, and several other mode values that result in server behavior more like standard SQL than how it operates by default.

• STRICT_ALL_TABLES and STRICT_TRANS_TABLES enable "strict" mode. In strict mode, the server is more restrictive about accepting bad data values. (Specifically, it rejects bad values rather than changing them to the closest legal value.) TRADITIONAL is another composite mode. It is like strict mode, but enables other modes that impose additional constraints for even stricter data checking. Traditional mode causes the server to behave like more traditional SQL servers with regard to how it handles bad data values. These modes are available as of MySQL 5.0.2.

When you set the SQL mode, specify a value consisting of one or more mode values separated by commas, or an empty string to clear the value. Mode values are not case sensitive.

To set the SQL mode when you start the server, use the --sql-mode option on the command line or in an option file:

--sql-mode="ANSI"
--sql-mode="ANSI_QUOTES,PIPES_AS_CONCAT"

To change the SQL mode at runtime, set the sql_mode system variable with a SET statement:

• Any client can set its own session-specific SQL mode:

  SET sql_mode = 'TRADITIONAL';
  

  
  

• To set the SQL mode globally, add the GLOBAL keyword:

  SET GLOBAL sql_mode = 'TRADITIONAL';
  

  
  

  Setting the global variable requires the SUPER administrative privilege. The value becomes the default SQL mode for clients that connect afterward.

To determine the current value of the session or global SQL mode, use these statements:

SELECT @@SESSION.sql_mode;
SELECT @@GLOBAL.sql_mode;

The value returned consists of a comma-separated list of enabled modes, or an empty value if no modes are enabled. The full set of mode values is given in the description of the sql_mode variable in Appendix D, "System, Status, and User Variable Reference." For discussion of SQL mode values that affect handling of erroneous or missing values during data entry, see "How MySQL Handles Invalid Data Values," in Chapter 3. General background on system variables is provided in "Setting and Checking System Variable Values," in Chapter 11.