9 replies to this topic

[yooj]

The title says it all. Is MySQL case sensitive?

[shri_bill]

Hi,

I'm a Linux user, as we know Unix-based operating systems are strictly a case-sensitive. But even on Linux, mysql client's commands, SQL commands (in accordance with SQL standards) and function-names etc. are NOT case-sensitive, i.e. both 'select' and 'SELECT' are perfectly valid, however entity-names such as names of tables and databases are case-sensitive, i.e. if a table's name is 'Customer', we cannot use 'customer' or 'CUSTOMER'. Once again, remember: even though table-names are case-sensitive, column-names are not.

HTH
Aditya Shrivastava
Let's create a "suffering free world" BKWSU.com .

Edited by shri_bill, 18 January 2011 - 02:47 AM.

[Orjan]

SQL itself isn't case sensitive, but it can be on searching data, all depends on the table collation settings.

[DarkLordCthulhu]

No.

[Alhazred]

MySQL syntax is not case sensitive, you can write
SELECT * FROM table WHERE ...
or
select * from table where...
or
SeLEct * FroM table WHerE
or whatever else you want.

On select queries you can search for case sensitive fields values, in example if you want to find "text" inside a field but not "TEXT", "Text".... you can use

SELECT * FROM table WHERE binary(fieldname)='text';

[shri_bill]

In all new database-clients (regardless of OS), interaction with the MySQL server is NOT case-sensitive. This includes: standard SQL keywords, entity names, function-names etc. However certain switches in command line (in terminal) ARE case-sensitive, such as a statement terminated with \G will format the output differently than a lower-case \g or a semicolon ( ; ).

Regarding the data within the database, I agree with "Alhazred" that we can use the binary() function.

However the client
Aditya Shrivastava

When I change the world will change. Let's create a "suffering free world" BKWSU.com .

[KaustavBanerjee]

mysql is never case-sensitive. and this is irrespective of the OS platform that you use.

SELECT * FROM EMP_TABLE
and
select * from emp_table

has the same meaning and definition. In fact, the results returned for both the queries is identical.

However, in several cases you might find query code where the language is somewhat like this -

select * from EMP_TABLE where NAME='ABC';
or
SELECT * FROM emp_table WHERE name='ABC';

All these patterns and types of coding syntax are used by the programmer himself.
This helps the programmer to keep track of difference between the sql command and the relation name or attribute name.

All the best

Kaustav Banerjee

Author and Owner of Programming Tutorial Blog site -
(http://www.a100websolutions.in)

[acidboy]

mysql is never case-sensitive. and this is irrespective of the OS platform that you use.

What on earth are you people talking about?

http://dev.mysql.com...ensitivity.html

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.

[WingedPanther73]

Okay, there are three contexts in which MySQL could be considered case sensitive:
1) low-level file structure: OS dependent
2) SQL syntax: no
3) how it treats Varchar data for comparison: this will depend on both the character set and the collation used.