6.4 Case Sensitivity of Identifiers and Reserved Words

Identifiers are used in several ways in SQL statements: They refer to databases, tables, columns, indexes, and aliases. One property that affects how you use identifiers is whether they're case sensitive; some identifiers are case sensitive and others are not. You should understand which is which and use them accordingly.

The rules that determine whether an identifier is case sensitive depend on what kind of identifier it is, and are described in the following items:

• For database and table names, case sensitivity depends on the operating system and filesystem of the server host, and on the setting of the lower_case_table_names system variable. Databases and tables are represented by directories and files, so if the operating system has case-sensitive filenames, MySQL treats database and table names as case sensitive. If filenames aren't case sensitive, database and table names are not either. Windows systems do not have case-sensitive filenames, but most Unix systems do. However, if the lower_case_table_names system variable is set to 1, database and table names are forced to lowercase before being used, which effectively results in case-insensitive behavior. (If you plan to use this variable, you should set it before creating any databases and tables.)

  Regardless of the case-sensitive properties of your filesystem, database and table names must be written consistently with the same lettercase throughout a query.

• Column names and index names are not case sensitive.

• Aliases are not case sensitive except aliases on table names.

Words such as SELECT, DELETE, WHERE, LIMIT, and VARCHAR are special in MySQL and are reserved as keywords. Reserved words are not case sensitive. They can be given in uppercase, lowercase, or even mixed case, and need not be written the same way throughout a query. The same is true for function names.