Thursday, March 23, 2017

SQL_MODE

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

Additional References

External Resources