Oracle 5.0, Справочное руководство

Страница: 774 / 3232

Identifier Case Sensitivity
754
database
db1
and from a table
t
in database
db2
in the same statement, you must refer to columns in
those tables as
db1.t.col_name
and
db2.t.col_name
.
The syntax
.tbl_name
means the table
tbl_name
in the default database. This syntax is accepted
for ODBC compatibility because some ODBC programs prefix table names with a “
.
” character.
9.2.2. Identifier Case Sensitivity
In MySQL, databases correspond to directories within the data directory. Each table within a database
corresponds to at least one file within the database directory (and possibly more, depending on the
storage engine). Consequently, the case sensitivity of the underlying operating system plays a part
in the case sensitivity of database and table names. This means database and table names are not
case sensitive in Windows, and case sensitive in most varieties of Unix. One notable exception is
Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive.
However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix. See
Section 1.8.4, “MySQL Extensions to Standard SQL” . The
lower_case_table_names
[466] system
variable also affects how the server handles identifier case sensitivity, as described later in this section.
Note
Although database and table names are not case sensitive on some platforms,
you should not refer to a given database or table using different cases within the
same statement. The following statement would not work because it refers to a
table both as
my_table
and as
MY_TABLE
:
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Column, index, and stored routine names are not case sensitive on any platform, nor are column
aliases. Trigger names are case sensitive, which differs from standard SQL.
By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The following
statement would not work on Unix, because it refers to the alias both as
a
and as
A
:
mysql> SELECT col_name FROM tbl_name AS a
-> WHERE a.col_name = 1 OR A.col_name = 2;
However, this same statement is permitted on Windows. To avoid problems caused by such
differences, it is best to adopt a consistent convention, such as always creating and referring to
databases and tables using lowercase names. This convention is recommended for maximum
portability and ease of use.
How table and database names are stored on disk and used in MySQL is affected by the
lower_case_table_names
[466] system variable, which you can set when starting
mysqld
.
lower_case_table_names
[466] can take the values shown in the following table. On Unix, the
default value of
lower_case_table_names
[466] is 0. On Windows the default value is 1. On Mac
OS X, the default value is 2.
Value Meaning
0
Table and database names are stored on disk using the lettercase specified in the
CREATE
TABLE
or
CREATE DATABASE
statement. Name comparisons are case sensitive. You
should not set this variable to 0 if you are running MySQL on a system that has case-
insensitive file names (such as Windows or Mac OS X). If you force this variable to 0 with
--lower-case-table-names=0
[466] on a case-insensitive file system and access
MyISAM
tablenames using different lettercases, index corruption may result.
1
Table names are stored in lowercase on disk and name comparisons are not case
sensitive. MySQL converts all table names to lowercase on storage and lookup. This
behavior also applies to database names and table aliases.
2
Table and database names are stored on disk using the lettercase specified in the
CREATE TABLE
or
CREATE DATABASE
statement, but MySQL converts them to