# Other Important Sql Modes

__*ANSI SQL mode*__
__*TRANDITIONAL SQL mode*__
__*ORACLE SQL mode*__
__*ONLY_FULL_GROUP_BY SQL mode*__
__*NO_ZERO_DATE SQL mode*__
__*ERROR_FOR_DIVISION_BY_ZERO SQL mode*__
__*NO_ENGINE_SUBSTITUTION SQL mode*__


+ Combination mode
  + `ANSI`: Change syntax and behavior to better conform to standard SQL
  + `TRANDITIONAL`: In this mode, TiDB behaves more like a "traditional" SQL database system
  + `ORACLE`: `PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER`  
+ Single behavior mode
  + `ONLY_FULL_GROUP_BY`: Cannot be executed when the SELECT list references a column that is not in the GROUP BY clause
  + `NO_ZERO_DATE`: Dates such as **'0000-00-00'** are not allowed 
  + `ERROR_FOR_DIVISION_BY_ZERO`: Return an error instead of NULL 
  + `NO_ENGINE_SUBSTITUTION`: TiDB only supports syntax and has no practical effect

--------------------------------------------------------------------------
For more information, see: `https://docs.pingcap.com/tidb/stable/sql-mode` 

Combination Modes

The ANSI and TRADITIONAL SQL modes are combination modes in MySQL that enable multiple modes. 

ANSI Mode
Some modes change how the SQL syntax is handled, and ANSI mode comprises several of these modes. By default, MySQL built-in functions are not reserved words, so you can create a column called count even though there is a COUNT() function. The “(” character must immediately follow the name “COUNT” to identify it as a function. The IGNORE_SPACE SQL mode permits spaces between a function name and the “(” character. This causes built-in function names to be treated as reserved words. As a result, you must enclose identifiers such as column names that are the same as function names in quotation marks. By default, the identifier quote is the backtick (`), but the ANSI_QUOTES SQL mode lets you use double quotation marks (") for the same purpose.

Single Behavior Modes
Most SQL modes change a single behavior. These include:
NO_ZERO_DATE, which disallows dates such as '0000-00-00’ that MySQL normally allows, even in strict mode.
ERROR_FOR_DIVISION_BY_ZERO, which raises an error and aborts any statement that attempts to divide a value by zero. When this mode is not enabled, MySQL evaluates a division by zero as NULL and raises a warning.

Note: For a full list of server SQL modes, see: https://dev.mysql.com/doc/mysql/en/sql-mode.html.


- ANSI equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE (syntax only)
- TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER (only (syntax support)
- ORACLE is equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER (syntax only)

- ONLY_FULL_GROUP_BY If the column that appears in GROUP BY does not appear in SELECT, HAVING, ORDER BY, this SQL is illegal because the query shows that the column not in GROUP BY does not appear to be normal (supported)
- NO_ZERO_DATE In strict mode, do not use '0000-00-00' as a legal date. You can still insert zero dates with the IGNORE option. In non-strict mode, this date is acceptable, but a warning is generated (supported)
- ERROR_FOR_DIVISION_BY_ZERO If this mode is enabled, the system generates an error when the divisor is 0 during INSERT or UPDATE. If this mode is not enabled, the system generates a warning when the divisor is 0, and NULL is substituted (supported)
- NO_ENGINE_SUBJECT Prevents automatic replacement of a storage engine if the required storage engine is disabled or not compiled (syntax only)