The default settings provided with the Win32 and Win64 builds of MySQL enforce a strict lowercase conversion for all the table and database names. This feature can be annoying for those working with auto-generated code tools (such as Entity Framework) because all the entities will be created in lower case as well. To overcome that we can use a useful, yet not widely known MySQL system variable who allows us to change the default behavior:
lower_case_table_names. It can easily be added to the MySQL my.cfg file, usually found in the following folder:
C:\Program Files (x86)\MySQL\MySQL Server 5.6\
The system variable must be placed under the [mysqld] section, just like that:
|
1 2 3 |
... [mysqld] lower_case_table_names = 2 |
- 0: Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. It's not advisable to set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X): the official documentation discourages that because there could be issues with MyISAM indexes files.
- 1: Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases. This means that all queries will be case-insensitive regarding db and/or table names (SELECT * FROM STUDENTS e SELECT * FROM students will give the exact same result); on the other side we won't be able to use uppercase characters at all, which bring us back to our main issue.
- 2: Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.
For an extensive overview of the
lower_case_table_names system variables we suggest to check out the official MySQL documentation:
