Understanding MySQL 5.7 SQL Mode: Default Values, Usage, and Practical Examples

MySQL can operate under different SQL Modes, which define the syntax rules and data validation checks the server enforces. The article begins by describing the purpose of SQL Mode and how it differs from other databases.

In MySQL 5.7 the default SQL Mode includes the following values: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION.

To view the current mode you can run:

root@database-one 22:48:  [(none)]> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                       |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

And to query the session value:

root@database-one 22:48:  [(none)]> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

The article explains three typical problems solved by setting SQL Mode: stricter data validation, compatibility with ANSI SQL, and easier data migration.

Commonly used mode values are described in detail, for example:

ANSI – makes MySQL behave more like standard SQL (REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY).

STRICT_TRANS_TABLES – aborts statements that would insert invalid data into transactional tables.

TRADITIONAL – combines several strict and safety modes.

Example session changes and their effects are shown. After setting the session to ANSI:

root@database-one 21:19:  [(none)]> select @@session.sql_mode;
| @@session.sql_mode |
+--------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------+

When STRICT_TRANS_TABLES is enabled, inserting a value that exceeds the column length triggers an error; without it, the value is truncated with a warning.

A complete table of all SQL Mode values supported by MySQL 5.7 is provided, including descriptions such as 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_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, PAD_CHAR_TO_FULL_LENGTH, REAL_AS_FLOAT, STRICT_ALL_TABLES, STRICT_TRANS_TABLES, etc.

Predefined mode combinations are also listed, for example:

ANSI = REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY

DB2 = PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS

TRADITIONAL = STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

Practical demonstration with a sample table emp shows how the mode influences INSERT behavior. After setting a strict mode, an attempt to insert a name longer than the defined VARCHAR(10) column results in error 1406; after removing the strict mode, the same insert succeeds with warnings and the value is truncated.

The article concludes with a reference to the original source and a brief author bio, emphasizing the author's experience in database architecture and operations.