MySQL case sensitivity
A good rule to remember - all is case sensitive (talking about identifiers), even if it is false it will help you to avoid many problems. But really in MySQL are case sensitive only table and database identifiers and only on platforms with case-sensitive filenames (Linux/Unix). It is so because tables and databases are represented as files (filenames are used as identifiers). And I think there is nothing good in such behavior because it complicates databases portability.
It is interesting, and what about MSSQL and Oracle.
Thursday 26 Oct 2006 | Coding, MySQL
“A good rule to remember - all is case sensitive (talking about identifiers), even if it is false it will help you to avoid many problems.”
Funny, I think it’s the other way around - “All is case insensitive, and if it’s not - make sure to alter the behaviour”
But really in MySQL are case sensitive only table and database identifiers and only on platforms with case-sensitive filenames (Linux/Unix).
Yes, well, as always “it depends”.
I admit I have been struggling a bit with the case-sensitivity of identifiers ever since I switched to linux. However, there is a way to make sure identifiers are not case sensitive. See:
http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html
“On Unix, the default value of lower_case_table_names is 0. On Windows the default value is 1. On Mac OS X, the default value is 2.”
I’m now following the suggestion in the manual:
“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 don’t see the names in their original lettercase.
…
Note that if you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before restarting mysqld with the new variable setting.”
Works great, I can live without the original lettercase: it is irrelevant because I want it to be case-insensitive anyway.
Regarding case sensitivity of data: I don’t think there are many RDBMS products that have such complete, advanced character set and collation support as MySQL. I know for fact that Oracle character support is kludgy, and involves explicitly calling a lot of conversion functions.
Roland BOuman