80

If I have a column names called category_id and Category_Id, are they different?

And if I have table called category and Category, are they different?

Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
shin
  • 31,901
  • 69
  • 184
  • 271

4 Answers4

94

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.

Seva Alekseyev
  • 59,826
  • 25
  • 160
  • 281
  • 1
    From MySQL documentation: Although database and table names are not case sensitive on some platforms, you should not refer to a given database or table using different cases within the same statement. The following statement would not work because it refers to a table both as my_table and as MY_TABLE: `SELECT * FROM my_table WHERE MY_TABLE.col=1;`, so quite correct, but should be treated as case sensitive in any case... – David M Jan 05 '10 at 20:54
  • 17
    Every table (***no matter what engine***) has a corresponding `.frm` file that stores the data structure. Since the names of the table and the file are the same, the case sensitivity of tables depends entirely on the case sensitivity of the file system on the server. – Andras Nemeth Nov 05 '12 at 15:30
  • @DavidM, The query works for me. Which filesystem are you using? – Pacerier Apr 08 '15 at 14:42
  • I think this answer might confuse the reader! Table COLUMNS are NOT case sensitive, this means `Size` and `size` is the same column. `INSERT INTO table_name (SIZE, gender) VALUES (123, 456);`will successfully insert the values in the columns `size` and `GeNdEr` – Sliq Apr 17 '19 at 12:35
  • That's exactly what I said in the second to last paragraph. "Field" and "column" are the same thing. – Seva Alekseyev Apr 23 '19 at 17:10
31

From the MySQL documentation:

database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive.

and

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

Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
Anitha.y
  • 311
  • 3
  • 2
5

For database and table names, it depends on the underlying operating system. See 8.2.2. Identifier Case Sensitivity

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
1

Strangely enough it seems to be case sensitive in the MySQL Workbench even on Windows.

We just tried to alter the results of a SELECT statement but the Workbench didn't let us, complaining that our query did not include the table's primary key (which it did but in lower-case) so the result was read-only. Running the same query with the primary key in proper case (ID instead of id) would let us edit the results as expected.

Thomas Touzimsky
  • 59
  • 3