Many operational characteristics of MySQL Server can be configured by setting the 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. This section discusses how to set the SQL mode. It's necessary to understand how to do this because references to the SQL mode occur throughout this study guide. The 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 name, it must be quoted. If the value contains a single mode name, quoting is optional. SQL mode values are not case sensitive, although this study guide always writes them in uppercase. Here are some examples:
To check the current sql_mode setting, select its value like this: mysql> SELECT @@sql_mode; +----------------------------------------------+ | @@sql_mode | +----------------------------------------------+ | STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO | +----------------------------------------------+ Some SQL mode values are composite modes that actually enable a set of modes. Values in this category include ANSI and trADITIONAL. To see which mode values a composite mode consists of, retrieve the value after setting it: mysql> SET sql_mode='TRADITIONAL"; Query OK, 0 rows affected (0.07 sec) mysql> SELECT @@sql_mode\G *************************** 1. row *************************** @@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL, NO_AUTO_CREATE_USER 1 row in set (0.03 sec) The MySQL Reference Manual lists all available SQL mode values. The following list briefly describes some of the values referred to elsewhere in this study guide:
Section 5.8, "Handling Missing or Invalid Data Values," provides additional detail about the use of strict and traditional SQL modes for controlling how restrictive the server is about accepting input data. |