# 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`** 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`**: Date such as `0000-00-00` is not allowed 
  + **`ERROR_FOR_DIVISION_BY_ZERO`**: Return an error instead of `NULL` when trying to save it to table 
  + **`NO_ENGINE_SUBSTITUTION`**: Syntax support only

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

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.

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.

Mode Examples:
- `ANSI` equivalent to `REAL_AS_FLOAT`, `PIPES_AS_CONCAT`, `ANSI_QUOTES`, `IGNORE_SPACE`
- `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`
- `ORACLE` is equivalent to `PIPES_AS_CONCAT`, `ANSI_QUOTES`, `IGNORE_SPACE`, `NO_KEY_OPTIONS`, `NO_TABLE_OPTIONS`, `NO_FIELD_OPTIONS`, `NO_AUTO_CREATE_USER`
- `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.
- `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.
- `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.
- `NO_ENGINE_SUBJECT` Prevents automatic replacement of a storage engine if the required storage engine is disabled or not compiled. (syntax only)