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?
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.