mysql手册,“1.7.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: 1. || is a string concatenation operator rather than a synonym for OR. 2. `"' 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. 3. 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"; 4. REAL is a synonym for FLOAT instead of a synonym for DOUBLE. 5. The default transaction isolation level is SERIALIZABLE. See section 6.7.4 SET TRANSACTION Syntax. 6. 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 从上得知,ONLY_FULL_GROUP_BY选项的用处在于上述第6条,我的理解就是可以在GROUP BY中使用字段的表达式!
2. `"' 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.
3. 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";
4. REAL is a synonym for FLOAT instead of a synonym for DOUBLE.
5. The default transaction isolation level is SERIALIZABLE. See section 6.7.4 SET TRANSACTION Syntax.
6. 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
从上得知,ONLY_FULL_GROUP_BY选项的用处在于上述第6条,我的理解就是可以在GROUP BY中使用字段的表达式!
其实我面临的问题就是ansi mode下面的不同,
只不过我找到的所有的reference manual都只有前5条。说来奇怪,本来我在mysql的网站上面看到的doc只有前5条,然后我在google中搜索最后一个的内容,然后再连到www.mysql.com,居然就有6个了。