Server SQL Modes in MySql

Server SQL Modes

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.

Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

For answers to questions often asked about server SQL modes in MySQL, see Section A.3, “MySQL 5.7 FAQ: Server SQL Mode”.

When working with InnoDB tables, consider also the innodb_strict_mode system variable. It enables additional error checks forInnoDB tables.

Setting the SQL Mode

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BYSTRICT_TRANS_TABLESNO_ZERO_IN_DATE,NO_ZERO_DATEERROR_FOR_DIVISION_BY_ZERONO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERONO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For additional discussion regarding these changes to the default SQL mode value, see SQL Mode Changes in MySQL 5.7.

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file.

Leave a Reply

Your email address will not be published. Required fields are marked *