It's possible to modify the way in which MySQL interprets certain parts of SQL, useful when porting queries from other databases. For example, the option PIPES_AS_CONCAT will persuade it to accept || as a concatenation operator rather than as a synonym for OR (see also gotcha 1.12). In 4.1 this facility has been extended considerably.

Firstly, the server SQL mode can be set globally or per session after server startup using SET [SESSION|GLOBAL] sql_mode='modes', where modes is a list of different modes separated by commas.

Secondly, some options (NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS) now enable mysqldump to output data definition statements without MySQL-specific extensions, making it easier to port MySQL databases to other servers.

And finally, there is also a list of special server modes (which act as aliases for certain combinations of mode values). This list includes DB2, MAXDB, MSSQL, ORACLE and POSTGRESQL (which all contain the same combination of values, although oddly enought not ONLY_FULL_GROUP_BY). Someone at MySQL must have a wicked sense of humour.

Reference:

• http://dev.mysql.com/doc/mysql/en/Server_SQL_mode.html