The are five kinds of identifiers that need to be considered in MySQL: databases, tables, columns, indexes and aliases. Databases, as stated are the containers for the tables and other associated database components. Tables hold the data that is used to produce information. Columns are the individual entities of the tables that contain the smallest portion of the data. Indexes in databases refer to the data structures that improve the speed of operations in a table. Aliases are temporary names assigned to database components. The identifiers that can be assigned to each component have their limitations:
• Size -
The following table describes the max length each database identifier can be assigned:
Identifier
|
Maximum Length
|
|---|---|
Database
|
64 Bytes
|
Table
|
64 Bytes
|
Column
|
64 Bytes
|
Index
|
64 Bytes
|
Alias
|
255 Bytes
|
The length of the identifier is in bytes, not characters. The maximum length will depend on the
byte count of all the characters used and should be taken into consideration if using multi-byte
characters in the identifier names.
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 determines the case sensitivity of database and table names. This means
database and table names are case sensitive in most varieties of Unix, and not case sensitive in Windows.
Although database and table names are not case sensitive on some platforms, database or table names should not be referred to 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;
Character Restrictions -
It is best practice to include only alphanumeric characters (and underscore or dashes) in the identifier names; however, other characters can be used if no other way is acceptable. Here is a list or restrictions that also need to be considered in such an event:
○ No identifier can contain ASCII 0 (0x00) or a byte with a value of 255
○ The use of identifier quote characters in identifiers is permitted, although it is best to avoid
doing so if possible
○ Database, table, and column names should not end with space characters
○ Before MySQL 5.1.6, database names and table names cannot contain “/”, “\”, “.”, or characters that are not allowed in a directory name
○ Identifiers may begin with a digit but unless quoted may not consist solely of digits
○ It is recommended that names of the form Me or MeN, where M and N are integers. For
example, avoid using 1e or 2e2 as identifiers, because an expression such as 1e+3 is
ambiguous. Depending on context, it might be interpreted as the expression 1e + 3 or as the
number 1e+3.
Comments
Post a Comment