MySQL Cross-Platform Table Naming

May 15, 2009

The upper, lower, or camel-casing of MySQL table names is rarely something developers consider. Unfortunately, it can cause major headaches if you are intending to port your application from a Windows or Mac development environment to a Linux or Unix-based host.

Table Name Case Sensitivity

Each table in a MySQL database corresponds to a physical .frm file within the database folder. Therefore, the case sensitivity of your tables relies on the underlying Operating System. For example, assume we have created the following table and filled it with data:


CREATE MyTable (
  id SMALLINT unsigned NOT NULL,
  name VARCHAR(50)
)

We then run the following query in our code:


SELECT id, name FROM mytable WHERE id = 1;

This query will work correctly on a Windows or Mac host. Unfortunately, it will fail once your application is copied to Linux or Unix.

Note that database, table, and trigger names are affected by case sensitivity. Columns, indexes, stored procedures, event names and aliases are not case-sensitive.

lower_case_table_names

lower_case_table_names is a MySQL system variable which controls how the database resolves table name casing. It can be passed as a start-up parameter for mysqld or within my.ini (although this is known to cause earlier versions of MySQL to fail). The value can be set to:

0 (Linux/Unix default)
Table name comparisons are case sensitive. Tables are stored on disk using the letter case specified.

1 (Windows default)
Table name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup.

2 (Mac default)
Table name comparisons are not case sensitive. MyISAM tables are stored on disk using the letter case specified, but MySQL converts them to lowercase on lookup. InnoDB tables are stored in lowercase.

It is therefore tempting to set lower_case_table_names to zero on your system. However, Windows users should not do that. The manual warns that if you set lower-case-table-names=0 on a case-insensitive file system and access MyISAM table names using different letter cases, index corruption may result. Nasty.

Recommendations

Personally, I prefer to use lowercase naming conventions for all databases, tables, indexes, keys and other MySQL names. That appears to be the easiest solution.