Server SQL Modes
The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the
sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.
Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.
For answers to questions often asked about server SQL modes in MySQL, see Section A.3, “MySQL 5.7 FAQ: Server SQL Mode”.
When working with
InnoDB tables, consider also the
innodb_strict_mode system variable. It enables additional error checks for
The default SQL mode in MySQL 5.7 includes these modes:
STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The
NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The
NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For additional discussion regarding these changes to the default SQL mode value, see SQL Mode Changes in MySQL 5.7.
To set the SQL mode at server startup, use the
--sql-mode=" option on the command line, or
sql-mode=" in an option file such as
my.cnf (Unix operating systems) or
modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using
--sql-mode="" on the command line, or
sql-mode="" in an option file.