How to Enforce Case Sensitive Table and Column Names in MySQL

MySQL : case sensitive field name while creating table

The manual says,

Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.

So a port depending on case sensitivity in column names won't work correctly.

It also has a lot to say about the case sensitivity of database and table names. That depends in intricate ways on the database server's host OS.

In my opinion @Strawberry is right. You're asking for trouble if you build a database depending on case sensitive identifiers.

Are column and table name case sensitive in MySQL?

On Unix, table names are case sensitive. On Windows, they are not. Fun, isn't it? Kinda like their respective file systems. Do you think it's a coincidence?

In other words, if you are developing on Windows but planning on deploying to a Linux machine, better test your SQL against a Linux-based MySQL too, or be prepared for mysterious "table not found" errors at prod time. VMs are cheap these days.

Field (column) names are case-insensitive regardless.

EDIT: we're talking about the operating system on the MySQL server machine, not client.

Are table names in MySQL case sensitive?

In general:

Database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.

In MySQL, databases correspond to directories within the data
directory. Each table within a database corresponds to at least one
file within the database directory. Consequently, the case sensitivity of the
underlying operating system plays a part in the case sensitivity of
database and table names.

One can configure how tables names are stored on the disk using the system variable lower_case_table_names (in the my.cnf configuration file under [mysqld]).

Read the section: 10.2.2 Identifier Case Sensitivity for more information.

Case Sensitivity of MySQL

I don't think you have to worry. The documentation is clear that column aliases are not case sensitive:

Column, index, stored routine, and event names are not case sensitive
on any platform, nor are column aliases.

. . .

By default, table aliases are case sensitive on Unix, but not so on
Windows or OS X.

If I had to speculate why the case sensitivity follows the default case sensitivity of the underlying operating system, I would guess that under some circumstances (particularly long ago when MySQL was being developed), tables are stored in files under their own names. Now it is controlled by the lower_case_table_names system variable, which simply has different defaults on different operating systems.

Are Table names in a Query supposed to be Case-SensitIvE?

From < Is SQL syntax case sensitive? >:

Mysql has a configuration option to enable/disable it. Usually case-sensitive table and column names are the default on Linux MySql and case-insensitive used to be the default on Windows, but now the installer asked about this during setup. For MSSQL it is a function of the database's collation setting.

Here is the MySql page about name case-sensitivity: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

Here is the article in MSDN about collations for MSSQL: http://msdn.microsoft.com/en-us/library/ms143503(SQL.90).aspx

---

Related Topics