MySQL: What is the benefit of non-ansi mode?

Question

Because I routinely work with a number of different databases, I make a point of switching MySQL to ANSI mode to bring some consistency into my code. This way I can use double quotes for column names and || for concatenation, which is normal for most databases (MSSQL, it appears doesn’t use ||).

Apart from compatibility, is there a benefit in not switching to ANSI mode in MySQL?

Answer 1

From the MySQL documentation:

ANSI
Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and (as of MySQL 5.7.5) ONLY_FULL_GROUP_BY.

The first four of these included modes are mostly minor cosmetic things, such as allowing pipes for string concatenation, and ignoring spaces between a function name and the ( character. From the point of view of porting your MySQL code to another database, these would be an annoyance, but would require fairly minor changes.

However, the ONLY_FULL_GROUP_BY mode is a different story. When this mode is turned off in MySQL (which would happen if ANSI were disabled), then it means you could have a GROUP BY query which selects columns not appearing in aggregates or in the GROUP BY clause. Such a query would likely fail completely on any other database, save perhaps MariaDB. In addition, if you write all your queries with the laxity of ONLY_FULL_GROUP_BY mode disabled, then you could be faced with a major refactor of many queries if you were to ever try porting your code to another database.

I would advise you to stick to ANSI stanadards. In the case of ONLY_FULL_GROUP_BY, adhering to it is just good database practice, and the other minor cosmetic requirements are things you may already be doing.

Answer 2

This is a bit long for a comment. ANSI mode -- which is well-explained in the documentation -- is basically a few stylistic changes to the language. These changes may seem minor, but they go against the grain of the history of MySQL.

In other words, using ANSI mode means that "traditional" MySQL code is not necessarily supported. Is that good or bad? Depends. If you need compatibility with other databases, then it is good. If you need compatibility with legacy code, then it is bad.