Identifiers & Qualifiers

In this section you will learn about describing the allowable syntax for identifiers in MySQL. The identifiers are Database, table, index, column and alias name. The following table describes the maximum length for each type of identifier.
      

Identifier	Maximum length
Database	64
table	64
column	64
Index	64
Alias	255

Some restriction on the character, which may appear in identifier are given below :

• No identifier can contain ASCII 0 or byte with a value of 255.
• Use of identifier quote character is permitted in identifiers. But avoidance of doing so if possible then it is best.
• The MySQL identifiers table, column and database should not end with space character.
• Database names cannot contain characters that are not allowed in directory name or  ' / ', ' \ ', ' - ',.
• Table name cannot contain  '/ ', ' \ ', ' - ', or character are not allowed in a filename.
• The identifier length is in bytes not in characters. But if you use multi-byte characters in identifier names then the maximum length will depend on the byte count of all the characters used.

MySQL allows names that have single identifier or multiple identifiers. The multiple part name of components can be separated by period ‘.’ Characters and initial parts of multiple-part name work as qualifiers that affect the context within which the final identifier is interpreted

You can refer to a column any of the following form in MySQL.  

• col_name: The column col_name that is used from which table in statement have a column of that name.
• tbl_name.col_name:- The column col_name from table tbl_name of the default database.
• db_name.tbl_name.col_name:-The column col_name from table tbl_name of the database db_name.

If a multiple-part name of any component need the quoting then quote them individually rather than the name as a whole. For example, you have to write ‘Emp-table’.’Emp-column’ rather than ‘Emp-table.Emp-column’. You should not specify tbl-name or db_name.tbl_name prefix to a column reference in a statement until the reference would be ambiguous. For example – Table1 and Table2 both have column age and we retrieve the column age by a SELECT statement that used by both table. In this situation, column age is ambiguous because it is not unique and both tables used it in the statement. That’s why we have to qualify the column age with a table name as table1.age or table2.age for indicating which table you mean.