Home arrow MySQL arrow Page 4 - Data Definition Language, Part 1

4.4 Identifier Syntax - MySQL

Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).

  1. Data Definition Language, Part 1
  2. 4.1 General Database and Table Properties
  3. 4.3 Limits on Number and Size of Database Components
  4. 4.4 Identifier Syntax
  6. 4.7 DROP TABLE
  7. 4.9 Creating and Dropping Indexes
  8. 4.10 Column Types
  9. 4.10.2 String Column Types
  10. 4.10.3 Date and Time Column Types
  11. 4.10.4 Column Options
  12. 4.10.5 Using the AUTO_INCREMENT Column Option
  13. 4.10.6 Automatic Type Conversion and Value Clipping
By: Sams Publishing
Rating: starstarstarstarstar / 41
January 19, 2005

print this article



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.

4.4.1 Legal Characters for 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.

4.4.2 Using Qualifiers for Table and Column Names

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.

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: