MySQL: naming rules
SQL or Structured Query Language is the language used to communicate with the MySQL server. Like any language it has it’s rules governing how you use it.
When refering to databases, tables, columns, indexes and aliases, you will have to obey the following:
- Identifier characters – can be any alphanumeric character (as long as it’s in the default character set) plus ‘_’ and ‘$’. They can also start with any legal character (legal to be included in the identifier), even a digit. Identifiers can be quoted within ‘`’ (backtick or `), which allows the use of any character except the backtick .
For database and table names (indentifiers), you can’t use the ‘.’ character, nor the pathname separator (‘/’, ‘\’). Your operating system may also have other constraints that you need to take note of.Column and table names (aliases) are pretty much unrestricted.
- Identifier length – identifiers can be up to 64 characters long.
- Identifier qualifiers – sometimes to avoid confusion and especially when the context does not indicate which database, table or column is referred to, it may be necessary to qualify which database, table or column a particular identifier refers to.
- With databases – just refer to it’s name
- With tables – either qualify both database and table (…FROM database_name.table_name) or the table_name by itself will use the default database. You can’t use the table_name unless a database has been selected.
- With columns – either:
- fully qualify the identifier: database_name.table_name.column_name
- partially qualify the identifier: table_name.column_name (using the default database)
- unqualified identifier: column_name which refers to the currently active table and database.
The case sensitivity rules vary depending on the operating system and part of the SQL statement.
Always use the same lettercase format in all your queries, regardless of the lettercase you have chosen.
- Keyword and function names are not case sensitive
- Database and table name case sensitivity depends on the way the host operating system treats filenames. It is therefore a good idea to stick to a particular lettercase (either upper or lower).
- Column and index names are not case sensitive.
- Alias names can be in any lettercase or mixed. Just always use the same format whenever you refer to them.
