MySQL Cross-Platform Table Naming

MySQL table namesThe 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:

Has MySQL table casing ever caught you out?

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • http://www.johngirvin.com johngirvin

    We got caught out once, way back, exactly as you describe. We had developed and tested the application on Windows, then scratched our heads for half an hour when we deployed to the production Linux servers and the logs filled with “table not found” errors.
     
    Now, I always use all lower case for everything!

  • http://www.mikehealy.com.au cranial-bore

    I’ve been caught out by other people’s code. TheY weRe coding in the SPirit of vaRIetY being the spIcE of Life :)

  • http://www.johngirvin.com johngirvin

    You need to be careful with the CapitalCasedAccessPorts as well.

  • http://www.cemerson.co.uk Stormrider

    I just make sure I get the correct case… but I rarely (if at all) use uppercase characters in table names, or column names.

  • nicolaken

    AS/400 DB2 always returns both table and ‘column’ names in uppercase. I always use uppercase.