MySQL - modes

What are modes?

Modes are settings that can be turned on and off. They change the behaviour and validations of MySQL.

To view the current global scope modes run this query -

SELECT @@GLOBAL.sql_mode;

To view the current session scope modes run this query -

SELECT @@SESSION.sql_mode;

If you start a new session and haven't changed any modes these global and session modes will be the same, as the global modes are the default.

A full list of modes and their properties can be found on the MySQL docs.

Changing modes

To change the global modes run -

SET GLOBAL sql_mode = '<list of modes>';

To change session modes run -

SET SESSION sql_mode = '<list of modes>';

If you change session modes, quitting and restarting the session will revert the modes back to the global modes, as these are the default, however, if you change the global modes, the only way to revert them back is to reset them. They won't revert back on a session restart.

The following queries show what session modes are currently active and removes a mode that returns a warning when trying to divide by zero.

SELECT @@SESSION.sql_mode;

The above query returns -

'ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION';

To change the session modes you just remove or add modes from the list. For example to remove the 'ERROR_FOR_DIVISION_BY_ZERO' mode, you just remove it from the list in the SET SESSION query , like we've done below.

SET SESSION sql_mode =
'ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
NO_ENGINE_SUBSTITUTION';

By removing the mode from the list and running the query, we've reset the modes without the 'ERROR_FOR_DIVISION_BY_ZERO' mode and therefore have removed that mode.

Adding and removing global modes is the same process except you use the below query -

SET GLOBAL sql_mode = '<list of modes>'

Running the SET GLOBAL sql_mode = '<list of modes>' and SET SESSION sql_mode = '<list of modes>' queries with empty strings instead of a list of modes will completely remove every mode from the global and session scopes respectively.