All the Perl that's Practical to Extract and Report

What do you think about case-sensitive databases?

jdavidb on 2008-12-04T14:58:15

I'm busy being disgusted with MySQL today for its case-sensitivity. I'm only slightly disgusted with it for being case-sensitive (against the standard, I think) on Linux. I'm more disgusted with it for turning around and not being the same on Windows!

Isn't case-insensitive table and column names the SQL standard? Is it better for a database to support the extra "feature" of allowing case-sensitive names, or is it a nightmare?

I'd appreciate intelligent comments added to this Stack Overflow question on the subject.

There's also this question about the SQL standard.

Update: I'd also appreciate it if people would vote up those two SO questions so as to (hopefully) get them more exposure and commentary. Although I'm not sure what the real chances are of old SO questions becoming "hot."

Are you using strict mode?

Alias on 2008-12-04T16:33:07

Standing question when people have problems with MySQL.

Re:Are you using strict mode?

jdavidb on 2008-12-04T16:48:49

Depends on which system we're on, which is one of our main problems.

MySQL installs with strict mode OFF by default on Linux (at least on the Fedora versions I've seen). The GUI installer on Windows, meanwhile, gives you a checkbox with strict mode enabled by default, which is a good idea in itself, but is a real problem when the default MySQL installation elsewhere is completely different.

Agreed...

janus on 2008-12-04T16:39:09

that's a nightmare... and another reason not to use the database in question (as if there weren't already enough reasons).

Actually we're using lowercase table and column names and uppercase keywords for ages now which makes the statements very good to read in my opinion.

This issue would be even more disgusting for us if we had to work with alien databases.
At least good to know what to take care of and to avoid... just in case... you never know.

The reason

dlc on 2008-12-04T17:00:04

The reason that database and table names are case-aware in mysql is because they're simply files and directories, and mysql punts to the underlying OS to handle it. Thus, on Windows, they're not case sensitive and on *nix they are. (BTW, I'm not defending it, I'm just pointing out why.)

Re:The reason

chromatic on 2008-12-04T18:38:01

I know that's true for MyISAM tables, but not all storage engines store each table in individual files. Is this true for them?

Re:The reason

dlc on 2008-12-04T20:11:19

The Views table has ENGINE=InnoDB, and the primary key is ViewID:

mysql> select count(ViewID) from Views\G
*************************** 1. row ***************************
count(ViewID): 0
1 row in set (0.00 sec)

mysql> select count(VIEWID) from Views\G
*************************** 1. row ***************************
count(VIEWID): 0
1 row in set (0.00 sec)

mysql> select count(ViewID) from VIEWS\G
ERROR 1146 (42S02): Table 'scimitar.VIEWS' doesn't exist

So InnoDB behaves the same way as MyISAM on Linux. I don't know about Windows.