Setting the Default SQL Mode

Server SQL modes define what SQL syntax MySQL should support and what kind of of data validation checks should it perform. This makes it easier to MySQL in different environments and touse MySQL togehter with other database servers. The MySQL Server apply these modes individually to different cleints.

Default SQL mode for MySQL 5.7

mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

Setting the SQL Mode

The mode can be set on the command line

$ mysqld --sql-mode="TRADITIONAL"

or in the option file

[mysqld] 
 sql-mode=TRADITIONAL

Change Mode during Runtime

For the session

SET SESSION sql_mode = 'modes';

or for the whole server

SET GLOBAL sql_mode = 'modes';

Important Modes

ANSI DB2 MAXDB MYSQL323 MYSQL40 ORACLE POSTGRESQL TRADITIONAL