Mastering SQL_MODE in MySQL: A Comprehensive Guide

1. What is SQL_MODE?

SQL_MODE is a system variable in the MySQL database that controls how MySQL processes SQL statements and data validation. It can be considered a set of constraints and specifications that ensure the accuracy, integrity, and consistency of data. For example, it can control how to handle invalid dates, whether to allow the insertion of incomplete records, whether to distinguish between uppercase and lowercase, etc.

In MySQL, setting SQL_MODE can solve the following types of problems:

1. Complete data validation of different strictness levels, effectively ensuring data accuracy.
2. Ensure that most SQL conforms to standard SQL syntax, so that when applications migrate between different databases, there is no need for major modifications to business SQL.
3. Before migrating data between different databases, setting SQL_MODE can make it easier to migrate data from MySQL to the target database.

By reading this article, you will gain the following points:

1. Default SQL_MODE values in different versions of MySQL.
2. Common SQL_MODE settings and their meanings.
3. The key role of SQL_MODE in data migration.

2. What are the values of SQL_MODE?

Default Values and Descriptions in MySQL 5.7

• ONLY_FULL_GROUP_BY: Columns that do not appear in the GROUP BY clause, if they appear in the SELECT list, HAVING condition, or ORDER BY condition, will be rejected.
• STRICT_TRANS_TABLES: Illegal dates, values exceeding the field length when inserted, will directly report an error and refuse to execute. For example, if you insert a value out of range into an integer column, an error will be triggered.
• NO_ZERO_DATE: For the date '0000-00-00', the logic is as follows:
  •   If SQL_MODE includes STRICT TRANS TABLES, the date is rejected for writing, but it can be written with the IGNORE keyword as '0000-00-00', with a warning.
  • disable: Can be inserted normally, without a warning.
  • enable: Can be inserted normally, with a warning.
• NO_ZERO_IN_DATE: For the month and date parts of the date, if they are 0, such as '2024-00-00', there are different execution logics:
  •   If SQL_MODE includes STRICT TRANS TABLES, the date is rejected for writing, but it can be written with the IGNORE keyword as '0000-00-00'.
  • disable: Can be inserted normally, the actual inserted value is still '2024-00-00' without a warning.
  • enable: Can be inserted normally, with a warning.
• ERROR_FOR_DIVISION_BY_ZERO: For a divisor of 0 (including MOD(N,0)), the execution logic is as follows:
  •   If SQL_MODE includes STRICTTRANSTABLES, the data is rejected for writing, but it can be written with the IGNORE keyword as NULL, with a warning.
  • disable: Insert NULL, without a warning.
  • enable: Insert NULL, with a warning.
• NO_AUTO_CREATE_USER: Prevents the creation of a user using a GRANT statement without a password clause.
• NO_ENGINE_SUBSTITUTION: When executing CREATE TABLE or ALTER TABLE statements, if the specified storage engine is not available, MySQL will throw an error instead of using the default storage engine.
  • disable: CREATE TABLE will be automatically replaced and executed, ALTER TABLE will not be executed, both commands have warnings.
  • enable: Both commands will report an error directly.

Default Values and Descriptions in MySQL 8.0

• STRICT_TRANS_TABLES: Same as MySQL 5.7, ensures that the data inserted or updated strictly complies with the table definition.
• NO_ZERO_DATE: Same as MySQL 5.7, prohibits the date field from being '0000-00-00', requiring valid dates to be entered.
• NO_ZERO_IN_DATE: Same as MySQL 5.7, prohibits the month or day part of the date or datetime field from being zero.
• ERROR_FOR_DIVISION_BY_ZERO: Same as MySQL 5.7, when dividing by zero, MySQL will throw an error instead of returning NULL.
• NO_AUTO_CREATE_USER: Same as MySQL 5.7, prohibits the automatic creation of users through GRANT statements.
• NO_ENGINE_SUBSTITUTION: Same as MySQL 5.7, ensures that the specified storage engine must exist and will not be automatically replaced.

Querying SQL_MODE

-- 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
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)

3. Common SQL_MODE Settings

SQL_MODE's common setting modes are a set of selectable options that can be combined as needed to change the database's behavior. Here are some common modes and their meanings:

• ANSI mode: Lenient mode, verifies the inserted data, and if it does not conform to the defined type or length, adjusts or truncates the data type and saves it, reporting a WARNING.
• STRICT_TRANS_TABLES mode: Strict mode, performs strict data validation, and erroneous data cannot be inserted, reporting an ERROR. Only effective for transactional tables.
• STRICT_ALL_TABLES mode: Strict mode, performs strict data validation, and erroneous data cannot be inserted, reporting an ERROR. Effective for all tables.
• TRADITIONAL mode: Strict mode, when inserting data into a MySQL database, performs strict data validation to ensure that erroneous data cannot be inserted, reporting an ERROR. When used in transactions, it will roll back the transaction.

In the following example, observe whether the illegal date "2007-04-31" (because April does not have 31 days) can be correctly inserted under different SQL_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> select @@session.sql_mode;
+--------------------------------------------------------------------------------+
| @@session.sql_mode                                                             |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (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> select @@session.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (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

It is clear that in ANSI mode, illegal dates can be inserted, but the inserted value becomes "0000-00-00 00:00:00" and the system gives a WARNING; while in TRADITIONAL mode, it will directly prompt that the date is illegal and refuse to insert. The STRICT_TRANS_TABLES mode and STRICT_ALL_TABLES mode are the same.

4. SQL_MODE Usage in Migration

When migrating from MySQL to other heterogeneous databases, the configuration and adjustment of SQL_MODE play a key role in the data migration process. Since different database systems have differences in SQL standards, data type processing, error handling, etc., the correct use of SQL_MODE can help ensure the integrity and consistency of data during the migration process.

SQL_MODE is a configuration option in MySQL that controls the parsing and execution of SQL statements.

For example, SQL_MODE can affect the handling of NULL values, date format validation, strictness of GROUP BY, etc. When migrating data to other databases, the target database may not support or handle these rules differently, so it is necessary to carefully configure and test SQL_MODE before migration.

4.1. SQL_MODE Adjustment Before Migration

Before migrating the MySQL database to another database, consider the following steps to adjust SQL_MODE to reduce potential compatibility issues during migration:

• Disable Strict Mode: Before migration, disabling the strict mode in MySQL (such as STRICT_TRANS_TABLES) can help identify which data may cause problems in the target database under the current configuration. By disabling strict mode, you can find and handle incompatible data in advance.

SET GLOBAL SQL_MODE = '';

• Disable ONLY_FULL_GROUP_BY: MySQL's ONLY_FULL_GROUP_BY mode requires all non-aggregated columns to be in the GROUP BY clause, which may not be necessary in other databases. Disabling this mode ensures that SQL queries can be executed correctly in the target database.
• Enable Lenient Mode: By setting a more lenient SQL_MODE (such as disabling NO_ZERO_DATE, NO_ZERO_IN_DATE), MySQL can accept some data formats that may be allowed in the target database and ensure that these data can be migrated smoothly.

4.2. Handling SQL_MODE Issues During Migration

• Date and Time Handling: Some database systems have stricter requirements for dates and times. For example, a date like 0000-00-00 may be legal in MySQL but cause an error in other databases. Before migration, it is necessary to ensure that data formats meet the requirements of the target database or clean up these data through SQL scripts.
• Handling of Empty Strings and NULL: In MySQL, empty strings and NULL may be considered equal in some cases, which is not the case in other databases. Before migration, the logic of these fields should be clarified, and conversions should be made when necessary.
• Case Sensitivity of Identifiers: MySQL's case sensitivity for identifiers may differ from that of the target database. Before migration, using the ANSI_QUOTES option of SQL_MODE can ensure that the reference method of identifiers complies with SQL standards and reduces compatibility issues in the target database.

4.3. Compatibility Testing After Migration

After completing the data migration, comprehensive compatibility testing is required in the target database to ensure that the migrated data and applications can run normally. Key areas for testing include:

• Accuracy of Query Results: Check whether SQL queries involving GROUP BY, aggregate functions, date processing, etc., return the expected results in the target database.
• Data Integrity: Ensure that the migrated data is not lost, truncated, or incorrectly converted.
• Performance: Some SQL_MODE settings may affect query performance, and related queries need to be optimized in the target database after migration.

4.4. Summary

When migrating from MySQL to other databases, properly adjusting SQL_MODE can significantly reduce compatibility issues during the migration process. By disabling strict modes, adjusting date and null value processing, ensuring the consistency of identifiers, and conducting comprehensive testing, you can ensure that the migrated data and applications run stably in the new database environment.

4.5. Notes During Migration

During the data migration process, SQL_MODE can be set to NO_TABLE_OPTIONS mode. This will remove the ENGINE keyword from SHOW CREATE TABLE to obtain a universal table creation script.

The test example is as follows:

``` bash

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>
mysql> set session sql_mode='NO_TABLE_OPTIONS';
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql>
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)

```

5. Conclusion

1. SQL_MODE's "strict mode" provides excellent data validation capabilities for MySQL, ensuring data accuracy. TRADITIONAL and STRICT_TRANS_TABLES are two commonly used strict modes, and it is important to note the differences between the two.
2. The various modes of SQL_MODE can be flexibly combined, and the combined mode can better meet the needs of applications. Especially in data migration, the use of SQL_MODE is even more important.

‍