Strict mode controls how MySQL handles invalid or missing values in statements that change data, such as in INSERT and UPDATE statements.
Strict mode (aka strict SQL mode) is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled.
In MySQL 8.0, the default SQL mode includes STRICT_TRANS_TABLES, which enables strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines.
We can disable strict mode in our session by removing it from our @@sql_mode variable. We can also disable strict mode at server startup, so that we don’t have to do it at runtime.
Example
First let’s take a look at my current @@sql_mode variable:
SELECT @@sql_mode;Result:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
My @@sql_mode variable is set to MySQL’s default SQL mode, which includes STRICT_TRANS_TABLES among other settings. As mentioned, the STRICT_TRANS_TABLES setting enables strict mode.
Our SQL mode can (and by default, does) contain various other settings, and so when we remove strict mode, we need to be sure that we don’t affect any of the other settings in the process.
With that in mind, we can disable strict mode like this:
SET @@sql_mode = sys.list_drop(@@sql_mode, 'STRICT_TRANS_TABLES');Result:
Query OK, 0 rows affected, 1 warning (0.00 sec)
That disables strict mode for our current session. We used the list_drop() system function to drop STRICT_TRANS_TABLES without affecting any of the other SQL modes. We could have alternatively gone SET @@sql_mode = ""; to clear our SQL mode altogether.
If your SQL mode contains STRICT_ALL_TABLES, then you’ll need to remove that, because that setting also enables strict mode.
Notice that we got a warning. Let’s take a look at it:
SHOW WARNINGS;Result:
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
It warns us that our SQL mode contains settings that should be used with strict mode.
As it turns out, NO_ZERO_DATE, NO_ZERO_IN_DATE, and ERROR_FOR_DIVISION_BY_ZERO are all generally considered part of strict mode, or at least related to strict mode. In fact, they were once merged into strict mode, but then this was later reverted, due to the issues that it caused. That said, the plan is still to remove these settings in a future release.
So, we have a decision to make. We can remove the above settings or leave them as is.
Let’s remove them:
SET @@sql_mode = sys.list_drop(@@sql_mode, 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO');Result:
Query OK, 0 rows affected (0.00 sec)
Done.
Now let’s take a look at our new SQL mode settings:
SELECT @@SESSION.sql_mode;Result:
ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION
As expected, strict mode has been disabled, and we’ve also removed the other settings that MySQL warned us about.
Setting the SQL Mode at Startup
It’s possible to set the SQL mode at server startup, so that we don’t have to change it at runtime.
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). Here, modes is a list of the desired SQL modes separated by commas. So if we don’t want strict mode to be enabled, we simply omit STRICT_TRANS_TABLES and STRICT_ALL_TABLES from our list.
We can also explicitly clear the SQL mode completely by setting it to the empty string using --sql-mode="" on the command line, or sql-mode="" in an option file. This would also result in strict mode being disabled.