SQL_MODE is a means of providing various SQL modes within MySQL that change the possible effect of statements. This system variable can be defined as either GLOBAL or SESSION scope.
mysql> SET [GLOBAL|SESSION] SQL_MODE = 'value[,value]';
The SQL_MODE can also be defined for a MySQL instance via the my.cnf configuration file.
[mysqld] sql_mode='value[,value]';
The list of valid SQL_MODES include:
- ALLOW_INVALID_DATES
- ANSI_QUOTES
- ERROR_FOR_DIVISION_BY_ZERO
- HIGH_NOT_PRECEDENCE
- IGNORE_SPACE
- NO_AUTO_CREATE_USER
- NO_AUTO_VALUE_ON_ZERO
- NO_BACKSLASH_ESCAPES
- NO_DIR_IN_CREATE
- NO_ENGINE_SUBSTITUTION
- NO_FIELD_OPTIONS
- NO_KEY_OPTIONS
- NO_TABLE_OPTIONS
- NO_UNSIGNED_SUBTRACTION
- NO_ZERO_DATE
- NO_ZERO_IN_DATE
- ONLY_FULL_GROUP_BY
- PAD_CHAR_TO_FULL_LENGTH
- PIPES_AS_CONCAT
- REAL_AS_FLOAT
- STRICT_ALL_TABLES
- STRICT_TRANS_TABLES
In addition, there are a number of SQL Server modes that combine one or more of these listed. These modes include:
- ANSI
- DB2
- MAXDB
- MSSQL
- MYSQL323
- MYSQL40
- ORACLE
- POSTGRESQL
- TRADITIONAL
