1.8.2 Running MySQL in ANSI Mode

If you start mysqld with the --ansi or --sql-mode=ANSI option, the following behaviours of MySQL Server change:

• || is a string concatenation operator rather than a synonym for OR.
• `"' is treated as an identifier quote character (like the MySQL Server ``' quote character) and not as a string quote character. You can still use ``' to quote identifers in ANSI mode. An implication of this is that you cannot use double quotes to quote a literal string, because it will be intepreted as an identifier.
• You can have any number of spaces between a function name and the `(' character. This forces all function names to be treated as reserved words. As a result, if you want to access any database, table, or column name that is a reserved word, you must quote it. For example, because there is a USER() function, the name of the user table in the mysql database and the User column in that table become reserved, so you must quote them:

  SELECT "User" FROM mysql."user";
  

• REAL is a synonym for FLOAT instead of a synonym for DOUBLE.
• The default transaction isolation level is SERIALIZABLE. See section 6.7.4 SET TRANSACTION Syntax.
• You can use a field/expression in GROUP BY that is not in the field list.

Running the server in ANSI mode is the same as starting it with these options:

--sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY
--transaction-isolation=SERIALIZABLE

In MySQL 4.1, you can achieve the same effect with these two statements:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode =
  "REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY";

In MySQL 4.1.1, the sql_mode options shown can be also be set with:

SET GLOBAL sql_mode="ansi";

In this case, the value of the sql_mode variable will be set to all options that are relevant for ANSI mode. You can check the result by doing:

mysql> SET GLOBAL sql_mode="ansi";
mysql> SELECT @@GLOBAL.sql_mode;
         -> "REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI"

User Comments

Add your own comment.