Like all programming models, MySQL identifiers follow certain rules and conventions.
Here are the rules to adhere to for naming identifiers to create objects in MySQL:
- Contain any alphanumeric characters from the default character set
- Include underscores (_) and dollar signs ($)
- Begin with any acceptable character, including digits
- Cannot be made up entirely of digits
- Cannot include a period (.)
- Cannot include an OS pathname separator, backslash (\) or forward slash (/)
Schema Object Names
Names for objects within MySQL - database, table, index, column, alias, view, stored procedure, partition, tablespace - are known as identifiers. This section describes the allowable syntax for identifiers in MySQL alongwith which types of identifiers are case sensitive and under what conditions.
An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.
A normal name usually comprises the alphanumeric character set from the current character set, underscore _ and $. MySQL Reserved words can be found in their documentation. An exception is when a reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.
CREATE TABLE create (select1 varchar(10));
CREATE TABLE `create` (`select` varchar(10));
INSERT INTO `create` VALUES('data1');
INSERT INTO `create` VALUES('data2');
SELECT `create`.select FROM `create`;
DROP TABLE `create`;
creates a table named create that contains a column named select.
The first creation fails, as the names are reserved words and not quoted.
The second create and the DML statements go through just fine.
As seen in the example, the normal identifier quote character is the backtick `:
mysql> SELECT * FROM `qtest` WHERE `qtest`.`id` = 201;
Using Double Quotes
If the ANSI_QUOTES SQL mode is enabled, it is allowable to quote identifiers within double quotes. Consequently, when this mode is not-enabled, string literals must be enclosed within single quotes. They cannot be enclosed within double quotes.
DROP TABLE `qtest`;
CREATE TABLE "qtest" (id INT);
CREATE TABLE "qtest" (id INT);
The first create table statement fails due to use of double-quotes: ERROR 1064: You have an error in your SQL syntax...
The second create succeeds once ANSI_QUOTES SQL mode is enabled.
The various settings of server SQL mode are described in the documentation.
Using Quote Within Names
Quote characters can be included within an identifier if we quote the identifier. If the character to be included within the identifier is the same as that used to quote the identifier itself, then we need to repeat that character twice:
DROP TABLE `c``est`;
CREATE TABLE `c``est` (`la"vie` VARCHAR(10));
INSERT INTO `c``est` VALUES ('one');
INSERT INTO `c``est`(`la"vie`) VALUES ('two');
SELECT `la"vie` FROM `c``est`;
creates a table named c`est that contains a column named la"vie.
Some basic DML statements are shown as well.
Identifiers may begin with a digit but and may consist solely of digits.
CREATE TABLE 1234 ( 789 varchar(10));
CREATE TABLE `1234` ( 1id SMALLINT, `789` varchar(10));
INSERT INTO `1234` VALUES(1,'digits1');
INSERT INTO `1234` VALUES(2,'digits2');
SELECT 1id, `789` FROM `1234`;
DROP TABLE `1234`;
creates a table named 1234 that contains two columns named 1id and 789.
The first create table statement fails due to missing backtick for 789: ERROR 1064: You have an error in your SQL syntax...
The 1id column does not need a backtick.
Some basic DML statements are included as well.
There are some restrictions on the characters that may appear in identifiers:
Note: Please avoid using 5e or 2e2 as identifiers, because where the names themselves make up an expression. Depending on context, it might be interpreted as the expression 1e + 3 or as an exponential number 1e+3.
Warning: Avoid using any algorithms such SHA or MD5 to generate table names because these programs can produce names in illegal or ambiguous formats.
A user variable cannot be used directly in an SQL statement as an identifier or as part of an identifier.
No identifier can contain ASCII 0 (0x00) or a byte with a value of 255.
Database, table, and column names should not end with space characters.
MySQL has relaxed some restrictions 5.1.6 onwards for database and table names. Earlier, these objects could contain characters that are not allowed in filenames, such as /, \, .. As of MySQL 5.1.6, special characters in database and table names are encoded in the corresponding filesystem names
The following table describes the maximum length for each type of identifier.
|Identifier||Max Length (chars)|
|Stored Function or Procedure||64|
|Log File Group||64|
Identifiers are stored using Unicode (UTF-8). This applies to identifiers in table definitions that are stored in .frm files and to identifiers stored in the grant tables in the mysql database. The allowable Unicode characters are those in the Basic Multilingual Plane (BMP). Supplementary characters are not allowed.
MySQL uses names with a single identifier or a multiple-part name with identifiers separated by a period ".". The initial parts of a multiple-part name affect the context to which the final identifier is related.
In MySQL, you can refer to a table column as column_name (read from first table with the column) or table_name.column_name (read from default database) or fully-qualified db_name.table_name.column_name.
Prefixing db_name or table_name is optional unless the reference to column would be ambiguous.
Note: When using quotes, quote components individually rather than name as a group. For example, write `some-table`.`some-column` and not `some-table.some- column`. Reserved words need not be quoted.
SELECT `category`.`name` FROM `category`;
SELECT `category`.`name` FROM `sakila`.`category`;
The components are selected specifying qualified names.
The syntax .tbl_name means the table tbl_name in the default database (Accepted for ODBC compatibility).
SELECT * FROM .category;
The category table has a DOT prefixing it in SELECT.
Mapping of Identifiers to Filenames
There is a correspondence between database and table identifiers and names in the filesystem. For the basic structure, MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory. For the table format files (.FRM), the data is always stored in this structure and location.
For the data and index files, the exact representation on disk is storage engine specific. These files may be stored in the same location as the FRM files, or the information may be stored separate file. InnoDB data is stored in the InnoDB data files. If you are using tablespaces with InnoDB, then the specific tablespace files you create are used instead.
Identifier Case Sensitivity
In MySQL, databases map to directories within the data directory and tables within a database map to one or more files within the database directory. Triggers are stored in files as well. As a result, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names.
As a result - database, table, and trigger names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. Mac OS X is a notable exception - though Unix-based but uses a default case-insensitive filesystem type (HFS+). UFS volumes on Mac OS X are case sensitive.
Note: Avoid mixing cases within the same statement:
mysql> SELECT * FROM Film WHERE FILM.film_id = 101;
Column names, column aliases, index names, stored routines and event names are not case sensitive on any platform.
However, names of triggers and logfile groups are case sensitive. This is a variation on standard SQL.
By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The following statement would work on Windows but not Unix, because it refers to the alias in mixed case.
mysql> SELECT rating FROM film AS f WHERE f.film_id = 101 OR F.rental_rate = 4.99;
Tip: For convention sake, if you use mixed case to name your database objects, it is best to use that convention throughout. Another common option is use of underscores to separate elements in a name, as in car_make.
To avoid such problems, for maximum portability and ease of use, it is best to be consistent conventionally, such as always creating and referring to databases and tables using lowercase names.
The lower_case_table_names system variable also affects how the server handles identifier case sensitivity while using table and database names as stored on disk. We can set this when starting mysqld. This variable does not affect case sensitivity of trigger identifiers.
On Unix, the default value of lower_case_table_names is 0. On Windows the default value is 1. On Mac OS X, the default value is 2.
|0||Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. Note that if you force this variable to 0 with --lower-case-table-names=0 on a case- insensitive filesystem and access MyISAM tablenames using different lettercases, index corruption may result.|
|1||Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.|
|2||Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.|
If you are going to be using MySQL on the same OS in all environments, you do not normally have to change the lower_case_table_names variable from its default value. Difficulties arise when transfering SQL scripts, table definitions etc between platforms with different case sensitivity for filesystems.
Tip:Though allowed on Unix, please avoid having two different tables named Rating and RATING
Avoiding Data Transfer Problems
To avoid data transfer problems arising from lettercase of database or table names, you have two options:
- Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.
- Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.
Exception: To avoid data transfer problems with InnoDB tables, set lower_case_table_names to 1 on all platforms to force names as lowercase.
Note for existing MySQL installations: If lower_case_table_names is to be changed to 1 on Unix, first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting.
Object names may be considered duplicates if their uppercase forms are equal according to a binary collation. That is true for names of cursors, conditions, functions, procedures, savepoints, and routine local variables. It is not true for names of columns, constraints, databases, partitions, statements prepared with PREPARE, tables, triggers, users, and user-defined variables.
Identifiers in MySQL Conclusion
In this lesson of the MySQL tutorial, we learnt the various aspects of Identifiers and their naming in MySQL.