4.4 Identifier Syntax - MySQL

When you write SQL statements, you use names to refer to databases and tables as well as to elements of tables such as columns and (sometimes) indexes. It's also possible to create aliases, which act as synonyms for table and column names. All of these types of names are known as identifiers; they identify a specific database element. This section describes the rules for writing identifiers.

Identifiers for databases, tables, columns, and indexes may be unquoted or quoted. An unquoted identifier must follow these rules:

• An identifier may contain all alphanumeric characters, the underline character (_), and the dollar sign ($).

• An identifier may begin with any of the legal characters, even a digit. However, it's best to avoid identifiers that might be misinterpreted as constants. For example, 1e3 might be taken as a number in scientific notation, whereas 0x1 might be interpreted as a hex constant, so neither is a good choice for an identifier.

• An identifier cannot consist entirely of digits.

An identifier may be quoted, in which case it can contain characters such as spaces or dashes that aren't otherwise legal. To quote an identifier, you may enclose it within backtick (´) characters. If the server was started with the --ansi or --sql-mode=ANSI_QUOTES option, you may also quote an identifier by enclosing it within double quotes ( "). Quoting causes the identifier syntax rules to be relaxed as follows:

• Any character may be used in a quoted identifier except characters with a numeric value of 0 or 255. For database and table names, other illegal characters are ., /, and \.

• A quoted identifier may consist entirely of digits.

An alias identifier can include any character, but should be quoted if it's a reserved word (such as SELECT or DESC), contains special characters, or consists entirely of digits. Aliases may be quoted within single quotes ( '), double quotes, or backticks.

Column and table identifiers can be written in qualified form—that is, together with the identifier of a higher-level element, with a period (.) separator.

A table name may be qualified with the name of the database to which it belongs. For example, the Country table in the world database may be referred to as world.Country (note the . separating the two identifiers in the name). If world is the default database, these statements are equivalent:

SELECT * FROM Country;
SELECT * FROM world.Country;

A column name may be qualified with the name of the table to which it belongs. For example, the Name column in the Country table may be referred to as Country.Name.

A further level of column qualification is possible because a table name may be qualified with a database name. So, another way to refer to the Name column is world.Country.Name. If world is the default database, the following statements are equivalent. They differ only in having successively more specific levels of name qualification:

SELECT Name FROM Country;
SELECT Country.Name FROM Country;
SELECT world.Country.Name FROM world.Country;

Sometimes qualifiers are necessary to resolve ambiguity. Other times you may elect to use them to make a statement clearer or more precise.