CODEWITHSUNDEEP
mysql
Tony
Tony

Reputation: 12705

MySQL CREATE TABLE script and the table name

I'm playing with MySQL (curiosity, self-learning) and I've noticed a strange thing:

I want to create a table:

use `a`;

CREATE TABLE `Languages` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `LanguageName` char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

but as the result, the table name is languages instead of Languages. Why ? Should I know something before create a table ?

Upvotes: 1

Views: 950

Answers (2)

Aaron
Aaron

Reputation: 57798

Check the value of your lower_case_table_names system variable. If this variable has a value of 1, then MySQL converts all table names to lowercase for storage. More information on this behavior can be found here: MySQL Identifier Case Sensitivity

MySQL actually recommends this behavior as a way to avoid data transfer issues (from the doc linked above):

To avoid data transfer problems arising from lettercase of database or table names, you have two options:

Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.

Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

Upvotes: 2

Mithrandir
Mithrandir

Reputation: 25397

SQL is case insensitive in general (keywords and names), so upper/lower cases should not matter. Since mysql maps tables to files (MyISAM tables at least) it makes difference wether you using a Linux or Windows system. Imho you should not create names, where case matters.

Upvotes: 1

Related Questions