83

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?

Improve this question
0

4 Answers 4

Reset to default
96

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.

Improve this answer
5
  • 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
    Commented Jan 5, 2010 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
    Commented Nov 5, 2012 at 15:30
  • @DavidM, The query works for me. Which filesystem are you using?
    – Pacerier
    Commented Apr 8, 2015 at 14:42
  • 1
    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
    Commented Apr 17, 2019 at 12:35
  • 1
    That's exactly what I said in the second to last paragraph. "Field" and "column" are the same thing.
    – Seva Alekseyev
    Commented Apr 23, 2019 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.

Improve this answer
5

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

Improve this answer
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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.