Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

  (email not shown publicly)

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Thursday December 04, 2008
09:58 AM

What do you think about case-sensitive databases?

[ #38019 ]

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."

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
  • Standing question when people have problems with MySQL.

    • 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.

      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
  • 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 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.)
    • I know that's true for MyISAM tables, but not all storage engines store each table in individual files. Is this true for them?

      • 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