Understanding MySQL SQL_MODE: Values, Settings, and Migration Practices

The article, authored by Xu Xiang of the iKang MySQL DBA team, introduces SQL_MODE , a MySQL system variable that controls how SQL statements are parsed, validated, and executed, ensuring data accuracy, completeness, and consistency.

SQL_MODE can enforce strict data checks, enforce standard‑compliant SQL syntax, and facilitate smoother data migration between heterogeneous databases.

The guide covers three main knowledge points: the default SQL_MODE values for different MySQL versions, the meaning of commonly used SQL_MODE options, and the critical role of SQL_MODE during data migration.

MySQL 5.7 default SQL_MODE includes the following options:

ONLY_FULL_GROUP_BY

STRICT_TRANS_TABLES

NO_ZERO_DATE

NO_ZERO_IN_DATE

ERROR_FOR_DIVISION_BY_ZERO

NO_AUTO_CREATE_USER

NO_ENGINE_SUBSTITUTION

-- MySQL 5.7
mysql> 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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL 8.0 default SQL_MODE is similar but omits NO_AUTO_CREATE_USER:

-- MySQL 8.0
mysql> 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_ENGINE_SUBSTITUTION               |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Common configuration modes include:

ANSI : relaxed mode that allows invalid dates but stores them as 0000-00-00 with a warning.

STRICT_TRANS_TABLES : strict mode that rejects invalid data with an error (applies to transactional tables).

STRICT_ALL_TABLES : strict mode for all tables.

TRADITIONAL : strict mode that also rolls back the transaction on error.

An example demonstrates how an illegal date '2007-04-31' behaves under different modes:

mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode='ANSI';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t_sql_mode_ansi(d datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_sql_mode_ansi values('2007-04-31');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t_sql_mode_ansi;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_sql_mode_ansi values('2007-04-31');
ERROR 1292 (22007): Incorrect datetime value: '2007-04-31' for column 'd' at row 1

The results show that the ANSI mode converts the illegal date to 0000-00-00 with a warning, while TRADITIONAL (and other strict modes) reject the insert outright.

SQL_MODE in migration

When migrating MySQL databases to other systems, adjusting SQL_MODE helps reduce compatibility issues. Recommended pre‑migration steps include disabling strict modes (e.g., STRICT_TRANS_TABLES ), disabling ONLY_FULL_GROUP_BY , and enabling a more relaxed mode to identify problematic data.

SET GLOBAL SQL_MODE='';

Additional adjustments such as disabling NO_ENGINE_SUBSTITUTION or enabling NO_TABLE_OPTIONS can simplify the generation of portable CREATE TABLE statements.

mysql> show create table test\G
*************************** 1. row ***************************
        Table: test
Create Table: CREATE TABLE `test` (
  `ename` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `hiredate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> set session sql_mode='NO_TABLE_OPTIONS';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show create table test\G
*************************** 1. row ***************************
        Table: test
Create Table: CREATE TABLE `test` (
  `ename` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `hiredate` date DEFAULT NULL
)
1 row in set (0.00 sec)

During migration, attention should be paid to date/time handling, empty‑string vs. NULL semantics, and identifier case sensitivity. After migration, comprehensive compatibility testing should verify query results, data integrity, and performance, as some SQL_MODE settings can affect query speed.

Conclusion

Strict SQL_MODE settings such as TRADITIONAL and STRICT_TRANS_TABLES provide strong data validation, while flexible combinations of modes allow tailoring to application needs, especially when migrating data to other database platforms.