At the beginning of a new project, one is faced with the question if MySQL's strict mode should be used or if the default behavior is sufficient for most scenarios. This mode certainly makes only sense if you evaluate the error codes and acting accordingly in the application - thus, it would be a bad idea to apply SQL modes to a running system. Unfortunately, SQL modes are not really common and have thereby a few bugs. Also the fact, that the user can bypass the mode in the session raises the question whether it makes sense to use these settings at all. I decided to only set a selected list of options; just what really makes sense and improve the behavior of MySQL. Strangely that these modes are not set by default, what one might expect, as they are close to a natural approach of databases usage.
I would like to peruse all options MySQL offer and set it under the view of obtaining the cleanest result in the database with the ability to recognize corrupt entries and respond to it accordingly in the application. Also a few settings to support lazy developers are used - but who is not lazy?
PIPES_AS_CONCAT
noI've always used CONCAT() to link together substrings. To me, it is more natural to use pipes for logical or's instead of using it as concatenating operator. Thus I turn this option off.
REAL_AS_FLOAT
noUsually, I always use the data types I really want. For that reason, I leave this option off, because in case of the usage of real, it should use double as in real world scenarios storage does not matter anymore (Okay, okay, it does matter, but only for optimizers).
ANSI_QUOTES
yesOne of the best options if you start a new project and want to be clean from the beginning.
STRICT_ALL_TABLES/STRICT_TRANS_TABLES
yesThese options are used to forbid new rows when NOT NULL values are missing, incorrect data types were used or columns are out of range. When creating more extensive tables, this would have an additional expense but I hope to be able to build a clean database with these settings.
IGNORE_SPACE
noIgnoring white spaces after function names spoils the possebelity to use the names otherwise without any further ado. But I think I always write the left parenthesis directly after the function name, so it does not matter.
NO_KEY_OPTIONS
noWe always want to see all MySQL specific features.
NO_TABLE_OPTIONS
noWe always want to see all MySQL specific features.
NO_FIELD_OPTIONS
noWe always want to see all MySQL specific features.
HIGH_NOT_PRECEDENCE
noA good and useful bugfix we keep turned on.
NO_AUTO_VALUE_ON_ZERO
noA useful feature for lazy people like me which should be turned on. If you import something into the database, this feature could be disabled temporarily.
NO_ZERO_DATE
noA very nice option to prohibit zero values in DATE columns. I handle this option in the application but I think for a general use, you should activate it to get notified if something really went wrong.
NO_ZERO_IN_DATE
yesThis option is nice and we want to get notified if a date is zero. In general this should not be happen, whereby this is a good exception candidate.
ERROR_FOR_DIVISION_BY_ZERO
noMy point of view is, that divisions by zero are no database relevant errors but the application has to verify the input and fetch up such errors.
NO_AUTO_CREATE_USER
noI use only GRANT's out of laziness and it would be not so good if suddenly something is missing for this reason. Therefore it remains off.
NO_BACKSLASH_ESCAPES
noShould be obsolete if you're using parameter bindings but using backslash escapes would be better than formatting every string as hex dump, if no parameter bindings are used. So we leave this turned on. Yes, I also know Unicode plays to break escape sequences, but if you know what you are doing, it should be safe!
NO_ENGINE_SUBSTITUTION
noUsing the default engine is now common practice. So we keep on.
NO_UNSIGNED_SUBTRACTION
noSubtracting unsigned values should be allowed. The developer has to deal with boundaries themselves.
ONLY_FULL_GROUP_BY
noIt is far too common, that I use more than one column besides the actual grouping column. Because this option brings no real benefit and no performance drop, I leave this option turned off.
So, we have the following result, or better said the following options for our my.ini file:
sql-mode="ANSI_QUOTES,STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE" But now the question of whether SQL-modes have a right to exist or not. I think a better default setting would make SQL-modes obsolete. Apparently, other developers see this as well, because MySQL forks like Drizzle completely throw away SQL-modes.
Who did not used SQL-modes before has certainly done well with it. Some options, however, are useful for a strict handling of the database. Many things are MySQL-specific options, since most of them increase the development comfort, which has certainly contributed not just a little to MySQL's success. In general we can say, that one needs to find a compromise between cleanliness and simplicity, but I think with the the settings above I've found it. I would be appreciate, if future versions of MySQL would be a little more restrictive and not by SQL-modes.