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.