MySQL can operate in different SQL modes. These can be set at server startup or at runtime.

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. Users can also set their own SQL mode at the session level via the @@sql_mode system variable.

By default, the sql_mode system variable contains a list of SQL modes that are applied.

As a user, we can modify our sql_mode system variable as required at runtime. This article demonstrates how we can remove a SQL mode from our sql_mode system variable.

Local sql_mode Variable

Before removing anything, we should check the current value of our @@sql_mode variable:

SELECT @@SESSION.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

This is the value of my session’s @@sql_mode variable.

Let’s remove the ONLY_FULL_GROUP_BY mode (and select the SQL mode again):

SET @@sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');
SELECT @@SESSION.sql_mode;

Result:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

We can see that the ONLY_FULL_GROUP_BY option no longer exists in our session’s SQL mode.

We have successfully removed a SQL mode from our session’s @@sql_mode variable.

Global sql_mode Variable

Note that the above code doesn’t affect the global SQL mode (it only affects the SQL mode for the current session). To verify, we can check the global sql_mode like this:

SELECT @@GLOBAL.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

As expected, we can see that the global SQL mode still contains ONLY_FULL_GROUP_BY. Therefore, we only updated the sql_mode for our local session.

If we have the SYSTEM_VARIABLES_ADMIN privilege (or the deprecated SUPER privilege), we can also update the global @@sql_mode variable.

Let’s remove STRICT_TRANS_TABLES from our global SQL mode:

SET @@GLOBAL.sql_mode = sys.list_drop(@@GLOBAL.sql_mode, 'STRICT_TRANS_TABLES');
SELECT @@GLOBAL.sql_mode;

Result:

ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

We can see that STRICT_TRANS_TABLES has been removed from the global SQL mode.

This hasn’t changed my session’s SQL mode though. My current session hasn’t changed, and it therefore still includes the STRICT_TRANS_TABLES mode:

SELECT @@SESSION.sql_mode;

Result:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

However, if I create a new connection to MySQL, this connection reflects the new global SQL mode.

Create a new connection:

mysql -uroot

Select the sql_mode of the new session:

SELECT @@SESSION.sql_mode;

Result:

ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

We can see that the STRICT_TRANS_TABLES is not in the SQL mode of the new session. The SQL mode reflects the global SQL mode. However, the new client can go ahead and change its own SQL mode by changing the SQL mode for the session like we did in the first example.