(MySql) How to disable Sql Mode like ONLY_FULL_GROUP_BY

MySql supports the concept of sql modes which are global configuration for every sql command. Sometimes one needs to remove particular sql mode like ONLY_FULL_GROUP_BY. Here is a quick guide about how to disable sql modes.

MySql - Disabling Specific SQL Mode

To find, you current sql mode, you can run following commands:

# for global sql mode
SELECT @@GLOBAL.sql_mode;

# for session sql mode
SELECT @@SESSION.sql_mode;

One can disable all sql modes by setting the sql mode to blank string.

SET SESSION sql_mode = ''

But if you want to disable a particular mode only, then you can first get your current sql mode and then reset it by removing the mode you want to remove.

For example, you want to remove ONLY_FULL_GROUP_BY, you can do following:

# Check current modes
SELECT @@SESSION.sql_mode;

> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE

# remove only a ONLY_FULL_GROUP_BY

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE'

Here are the relevant excerpt from mysql documentation for default modes:

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.