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 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.
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.
- 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?
Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.