MySQL Naming Rules

Almost every SQL statement refers in some way to a database or its constituent elements. This section describes the syntax rules for referring to databases, tables, columns, indexes, and aliases. Names are subject to case sensitivity considerations, which are described as well.

Referring to Elements of Databases

When you use names to refer to elements of databases, you are constrained by the characters you can use and the length that names can be. The form of names also depends on the context in which you use them:

• Legal characters in names. Names may consist of any alphanumeric characters in the character set used by the server, plus the characters '_' and '$'. Names may start with any character that is legal in a name, including a digit. However, a name may not consist solely of digits because that would make it indistinguishable from a number. The ability that MySQL provides to begin a name with a number is unusual. If you use such a name, be particularly careful of names containing an 'E' or 'e' because those characters can lead to ambiguous expressions. 23e + 14 means column 23e plus 14, but what about 23e+14? Does it mean the same thing, or is it a number in scientific notation?

• Name length. Names for databases, tables, columns, and indexes may be up to 64 characters long. Alias names may be up to 256 characters long.

• Name qualifiers. To refer to a database, just specify its name:

  USE db_name
  SHOW TABLES FROM db_name
  								

  To refer to a table, you have two choices. A fully qualified table name consists of a database name and a table name:

  SHOW TABLES FROM db_name.tbl_name
  SELECT * FROM db_name.tbl_name
  								

  A table name by itself refers to a table in the default (current) database. If samp_db is the default database, the following statements are equivalent:

  SELECT * FROM member
  SELECT * FROM samp_db.member
  

  To refer to a column, there are three choices: fully qualified, partially qualified, and unqualified. A fully qualified name (written as db_name.tbl_name.col_name) is completely specified. A partially qualified name (written as tbl_name.col_name) refers to a column in the named table. An unqualified name (written simply as col_name) refers to whatever table is indicated by the surrounding context. The following two queries refer to the same column names, but the context supplied by the FROM clauses indicates which tables to select the columns from:

  SELECT last_name, first_name FROM president
  SELECT last_name, first_name FROM members
  

  It's usually unnecessary to supply fully qualified names, although it's always legal to do so if you like. If you select a database with a USE statement, that database becomes the default database and is implicit in every unqualified table reference. If you're using a SELECT statement that refers to only one table, that table is implicit for every column reference in the statement. It's necessary to qualify names only when a table or database cannot be determined from context. Here are some situations in which ambiguity arises:

  • Queries that refer to tables from multiple databases. Any table not in the default database must be referenced using the db_name.tbl_name form to let MySQL know which database to look in to find the table.

  • Queries that select a column from multiple tables, where more than one table contains a column with that name.

Case Sensitivity in SQL Statements

Case sensitivity rules in SQL statements vary for different parts of the statement, and also depend on what you referring to and the operating system on which the server is running:

• SQL keywords and function names. Keywords and function names are not case sensitive. They may be given in any lettercase. The following statements are equivalent:

  SELECT NOW()
  select now()
  sElEcT nOw()
  

• Database and table names. Databases and tables in MySQL correspond to directories and files in the underlying file system on the server host. As a result, case sensitivity of database and table names depends on the way the operating system on that host treats filenames. A server running on UNIX treats database and table names as case sensitive because UNIX filenames are case sensitive. Windows filenames are not case sensitive, so a server running on Windows does not treat database and table names as case sensitive.

  You should be aware of this characteristic if you create a database on a UNIX server that you might someday move to a Windows server: If you create two tables named abc and ABC, they would not be distinguishable on a Windows machine. One way to avoid having this become an issue is to pick a given lettercase (for example, lowercase) and always create databases and tables using names in that lettercase. Then case of names won't be an issue if you move a database to a different server.

• Column and index names. Column and index names are not case sensitive in MySQL. The following queries are equivalent:

  SELECT name FROM student
  SELECT NAME FROM student
  SELECT nAmE FROM student
  

• Alias names. Aliases are case sensitive. You can specify an alias in any lettercase (upper, lower, or mixed), but you must refer to it elsewhere in the query using the same case.

Regardless of whether or not a database, table, or alias name is case sensitive, you must refer to a given name from any of those categories using the same lettercase throughout a query. That is not true for SQL keywords; function names; or column and index names; which may be referred to in varying lettercase style throughout a query. Naturally, the query will be more readable if you use a consistent lettercase rather than "ransom note" style (SelECt NamE From …).