Identifiers

Identifiers are names you make up to reference database objects. In MySQL, database objects consist of databases, tables, and columns. These objects fit into a hierarchical namespace whose root element is the database in question. You can reference any given object on a MySQL server—assuming you have the proper rights—using one of the following conventions:

Absolute naming

Absolute naming is specifying the full tree of the object you are referencing. For example, the column BALANCE in the table ACCOUNT in the database BANK would be referenced absolutely as: 

BANK.ACCOUNT.BALANCE
Relative naming

Relative naming allows you to specify only part of the object’s name, with the rest of the name being assumed based on your current context. For example, if you are currently connected to the BANK database, you can reference the BANK.ACCOUNT.BALANCE column simply as ACCOUNT.BALANCE. In an SQL query where you have specified that you are selecting from the ACCOUNT table, you can reference the column using only BALANCE. You must provide an extra layer of context whenever relative naming might result in ambiguity. An example of such ambiguity would be a SELECT statement pulling from two tables that both have BALANCE columns.

Aliasing

Aliasing enables you to reference an object using an alternate name that helps avoid both ambiguity and the need to fully qualify a long name.

In general, MySQL allows you to use any character in an identifier.[1] This rule is limited, however, for databases and tables, because these values must be treated as files on the local filesystem. You can therefore use only characters valid for the underlying filesystem’s filenaming conventions in a database or table name. Specifically, you may not use / or . in a database or table name. You can never use NUL (ASCII 0) or ASCII 255 in an identifier.

Given these rules, it is very easy to shoot yourself in the foot when naming things. As a general rule, it is a good idea to stick to alphanumeric characters from whatever character set you are using.

When an identifier is also an SQL keyword, you must enclose the identifier in backticks: 

CREATE TABLE `select` ( `table` INT NOT NULL PRIMARY KEY AUTO_INCREMENT);

Since Version 3.23.6, MySQL supports the quoting of identifiers using both backticks and double quotes. For ANSI compatibility, however, you should use double quotes for quoting identifiers. You must, however, be running MySQL in ANSI mode.


[1] Older versions of MySQL limited identifiers to valid alphanumeric characters from the default character set as well as $ and _.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset