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?
Add a comment
|
4 Answers
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.
5 Comments
David M
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...
Andras Nemeth
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.
Pacerier
@DavidM, The query works for me. Which filesystem are you using?
Sliq
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
Seva Alekseyev
That's exactly what I said in the second to last paragraph. "Field" and "column" are the same thing.
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.
Comments
For database and table names, it depends on the underlying operating system. See 8.2.2. Identifier Case Sensitivity
Comments
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.
