Stored Routines to easily work with the SQL_MODE

Versione italiana

Working with the SQL_MODE can be tricky, because it’s a command separated list involving long keywords. To be honest, I hate to rewrite the flags, trying to read an unreadable list, etc. Of course some string functions can help (Justin Swanhart suggests to use REPLACE() to make comma-separated lists readable).

I made a small set of Stored Routines wich allow me to easily show SQL_MODE, add a flag, drop a flag and check if a flag is set. These routines work with the GLOBAL SQL_MODE; if you don’t like this, simply replace “@@global.” with “@@session.” in the SQL file before installing.

You can download (or improve) my routines using this SQL_MODER GitHub repo.

And now, some examples.

Show currently selected flags:

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
8 rows in set (0.46 sec)

(this is the SQL_MODE I suggest to use)

Check wether a flag is set:

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.38 sec)

Add one or more flags:

MariaDB [(none)]> CALL _.sql_mode_set('NO_UNSIGNED_SUBTRACTION,HIGH_NOT_PRECEDEN
CE');
Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| HIGH_NOT_PRECEDENCE        |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_UNSIGNED_SUBTRACTION    |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
10 rows in set (0.13 sec)

Query OK, 0 rows affected (2.09 sec)

Drop a flag:

MariaDB [(none)]> CALL _.sql_mode_unset('HIGH_NOT_PRECEDENCE');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

If you mistyped the flag (or it wasn’t set):

MariaDB [(none)]> CALL _.sql_mode_unset('hello world');
ERROR 1644 (45000): Flag 'hello world' was not set

Enjoy!