(mysql.info) legal-names

Info Catalog

(mysql.info) literals

(mysql.info) language-structure

(mysql.info) user-variables

 
 9.2 Database, Table, Index, Column, and Alias Names
 ===================================================
 

Menu

 
* identifier-qualifiers        Identifier Qualifiers
* name-case-sensitivity        Identifier Case Sensitivity
 
 Database, table, index, column, and alias names are identifiers.  This
 section describes the allowable syntax for identifiers in MySQL.
 
 The following table describes the maximum length and allowable
 characters for each type of identifier.
 
 *Identifier**Maximum    *Allowed Characters*
             Length      
             (bytes)*    
 Database    64          Any character that is allowed in a directory
                         name, except ‘`/'’, ‘`\'’, or ‘`.'’
 Table       64          Any character that is allowed in a filename,
                         except ‘`/'’, ‘`\'’, or ‘`.'’
 Column      64          All characters
 Index       64          All characters
 Alias       255         All characters
 
 In addition to the restrictions noted in the table, no identifier can
 contain ASCII 0 or a byte with a value of 255. Database, table, and
 column names should not end with space characters. The use of
 identifier quote characters in identifiers is permitted, although it is
 best to avoid doing so if possible.
 
 Identifiers are stored using Unicode (UTF-8). This applies to
 identifiers in table definitions that stored in `.frm' files and to
 identifiers stored in the grant tables in the `mysql' database. The
 sizes of the string columns in the grant tables (and in any other
 tables) in MySQL 5.0 are given as number of characters. This means that
 (unlike some earlier versions of MySQL) you can use multi-byte
 characters without reducing the number of characters allowed for values
 stored in these columns.
 
 An identifier may be quoted or unquoted. If an identifier is a reserved
 word or contains special characters, you _must_ quote it whenever you
 refer to it.  (Exception: A word that follows a period in a qualified
 name must be an identifier, so it is not necessary to quote it, even if
 it is a reserved word.) For a list of reserved words, see 
 reserved-words. Special characters are those outside the set of
 alphanumeric characters from the current character set, ‘`_'’, and
 ‘`$'’.
 
 The identifier quote character is the backtick (‘``'’):
 
      mysql> SELECT * FROM `select` WHERE `select`.id > 100;
 
 If the `ANSI_QUOTES' SQL mode is enabled, it is also allowable to quote
 identifiers within double quotes:
 
      mysql> CREATE TABLE "test" (col INT);
      ERROR 1064: You have an error in your SQL syntax. (...)
      mysql> SET sql_mode='ANSI_QUOTES';
      mysql> CREATE TABLE "test" (col INT);
      Query OK, 0 rows affected (0.00 sec)
 
 Note: Because `ANSI_QUOTES' causes the server to interpret
 double-quoted strings as identifiers, string literals must be enclosed
 within single quotes. They cannot be enclosed within double quotes when
 `ANSI_QUOTES' is enabled.
 
 The server SQL mode is controlled as described in 
 server-sql-mode.
 
 Identifier quote characters can be included within an identifier _if
 you quote the identifier_. If the character to be included within the
 identifier is the same as that used to quote the identifier itself,
 then you need to double the character. The following statement creates
 a table named `a`b' that contains a column named `c"d':
 
      mysql> CREATE TABLE `a``b` (`c"d` INT);
 
 It is recommended that you do not use names of the form `Me' or `MeN',
 such as `1e' or `2e2', because an expression such as `1e+3' is
 ambiguous.  Depending on context, it might be interpreted as the
 expression `1e + 3' or as the number `1e+3'.
 
 Be careful when using `MD5()' to produce table names because it can
 produce names in illegal or ambiguous formats such as those just
 described.
 

Info Catalog

(mysql.info) literals

(mysql.info) language-structure

(mysql.info) user-variables