본문 바로가기

MariaDB, MySQL

MySQL 5.7 SQL Modes

sql_mode

Default SQL mode

  • NO_ENGINE_SUBSTITUTION
  • From 5.7.5 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES
  • From 5.7.7 NO_AUTO_CREATE_USER
  • From 5.7.8 NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_IN_DATE

Set sql_mode

in my.cnf

  • sql-mode="value1,value2"

 

dynamic

  • SET GLOBAL sql_mode = 'modes';
  • SET SESSION sql_mode = 'modes';

Full List of SQL Modes

(based on 5.7.23)

 

Strict SQL mode

  • STRICT_ALL_TABLES - Enable strict SQL mode for all storage engines.
  • STRICT_TRANS_TABLES - (Default) Enable strict SQL mode for transactional storage engines.

 

Data integrity

  • ALLOW_INVALID_DATES
  • ERROR_FOR_DIVISION_BY_ZERO (Default)
  • NO_ZERO_DATE (Default)
  • NO_ZERO_IN_DATE (Default)
  • PAD_CHAR_TO_FULL_LENGTH

 

SQL

  • ANSI_QUOTES
  • HIGH_NOT_PRECEDENCE
  • ONLY_FULL_GROUP_BY (Default)
  • PIPES_AS_CONCAT
  • REAL_AS_FLOAT
  • NO_UNSIGNED_SUBTRACTION
  • NO_AUTO_VALUE_ON_ZERO

 

Client

  • NO_FILES_OPTIONS
  • NO_KEY_OPTIONS
  • NO_TABLE_OPTIONS

 

Etc

  • NO_AUTO_CREATE_USER (Default)
  • NO_ENGINE_SUBSTITUTION (Default)
  • NO_DIR_IN_CREATE
  • IGNORE_SPACE
  • NO_BACKSLASH_ESCAPES

Combination SQL Modes

  • ANSI = REAL_AS_FLOAT + PIPES_AS_CONCAT + ANSI_QUOTES + IGNORE_SPACE + ONLY_FULL_GROUP_BY
  • ORACLE= PIPES_AS_CONCAT + ANSI_QUOTES + IGNORE_SPACE + NO_KEY_OPTIONS + NO_TABLE_OPTIONS + NO_FILED_OPTIONS + NO_AUTO_CREATE_USER
    ( 5.7.22 Will be deprecated or removed)

Strict SQL Mode

Enabled when using STRICT_ALL_TABLES or STRICT_TRANS_TABLES.

This mode controls how MySQL handles invalid or missing values in data-change statements.

 

Under strict SQL Mode

  • Index creation failed when key exceed maximum_key_length
  • For transaction tables, Error occurs for invalid or missing values in data-change statement
Operation Strict mode
Enabled Disabled
Division by zero Insert NULL with warning Insert NULL without warning and error
Insert ZERO date - '0000-00-00' Not permitted and inserts produce an error Insert without warning
Insert ZERO in date - '2010-00-01', '2010-01-00' Not permitted and inserts produce an error Insert without warning

The following options only valid when use with strict sql mode.

  • NO_ZERO_DATE (Default) '0000-00-00'
  • NO_ZERO_IN_DATE (Default) '2019-00-00'