标识符对大小写敏感的情况
是否对标识符敏感跟使用的操作系统有很大关系。以unix为基础的系统对大小写敏感,windows对大小写不敏感,如果是苹果系统通常对大小写不敏感, Whether objects are case-sensitive or not is partly determined by the underlying operating system. Unix-based systems are case-sensitive, Windows is not, while Mac OS X is usually not, but can be if UFS volumes are used.
Database, table, table aliases and trigger names are affected by the systems case-sensitivity, while index, column, column aliases, stored routine and event names are never case sensitive.
Log file group name are case sensitive.
The lower_case_table_names server system variable plays a key role. It determines whether table names, aliases and database names are compared in a case-sensitive manner. If set to 0 (the default on Unix-based systems), table names and aliases and database names are compared in a case-sensitive manner. If set to 1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner. If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase.
It is thus possible to make Unix-based systems behave like Windows and ignore case-sensitivity, but the reverse is not true, as the underlying Windows filesystem can not support this.
Even on case-insensitive systems, you are required to use the same case consistently within the same statement. The following statement fails, as it refers to the table name in a different case.
SELECT * FROM a_table WHERE A_table.id>10;
For a full list of identifier naming rules, see Identifier Names.