MySQL Cross-Platform Table Naming

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.

See also:

• MySQL manual: Identifier Case Sensitivity
• MySQL: the Pros and Cons of MyISAM Tables
• MySQL: the Pros and Cons of InnoDB Tables
• How to Use MySQL Foreign Keys for Quicker Database Development

Has MySQL table casing ever caught you out?

Related posts:

1. Free Performance with MySQL Table Types Last week, SitePoint launched its second Twitaway, giving away a...
2. MySQL: the Pros and Cons of MyISAM Tables MySQL offers several database table types, but which is suitable...
3. MySQL: the Pros and Cons of InnoDB Tables Craig discusses why the lesser-used MySQL InnoDB table engine might...
4. How to Install MySQL Installing MySQL is easier than you think. Craig provides a...
5. 11 Virtual Machine Solutions To Ease Your Cross-Platform Checks Who needs two computers when you have virtual machines? Sean...

This entry was posted on Friday, May 15th, 2009 at 4:44 pm, contains 459 words, 7 images, and is filed under Web Tech. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed. The views and opinions in this blog post are those of its author.