Is data stored in tables case sensitive?

Question

I have three table samples. I have used mysql to store data in database

+--------------------------+
| Table-1                  |
+--------------------------+
| Sl.No | Name | City      |
+-------+------+-----------+
|  1    | Carl | Australia |
+-------+------+-----------+

+--------------------------+
| Table-1                  |
+--------------------------+
| Sl.No | Name | City      |
+-------+------+-----------+
|  1    | carl | australia |
+-------+------+-----------+

+--------------------------+
| Table-1                  |
+--------------------------+
| Sl.No | Name | City      |
+-------+------+-----------+
|  1    | CARL | AUSTRALIA |
+-------+------+-----------+

clearly we can see one row of data in each table
nature of data is same

what I have done is I have used different case letters some are uppercase letters and some are lowercase letters.

Are data stored in database case sensitive?

"case-sensitive" for what purpose? The stored data is just data. It's not interpreted as anything, not executed as code, it's just raw data. Whatever you *do* with that data may or may not be case-sensitive. At a raw data level, the binary representation of "Carl" and "CARL" are different, so the data is different. But whatever uses that data may not care about that difference. — David, Oct 03 '13 at 17:09
Data is stored the way it was entered (caps, lower case, mix), but MyQL SQL select statements are case insensitive by default: http://stackoverflow.com/questions/7857669/mysql-case-sensitive-query — Germann Arlington, Oct 03 '13 at 17:15

Rahul Tripathi · Answer 1 · 2013-10-03T17:39:37.370

Data is just stored as raw data. However if you want to get the data in any particular format you can format it. You may insert the data as per your convinience however internally they are interpreted the same ie, ABC is same as abc and aBc

By default MySQL queries are not case-sensitive.

From the MySQL site

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. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix.

On a side note:-

Inside the database, all data is stored in binary format. You can have different data types which is used by the computer to interpret how to show the data to the user, but that is just a mask.

score 5 · Accepted Answer · answered Oct 03 '13 at 17:11

yes, the database stores the data how you submit it.

if you say:

INSERT INTO MyTable (LowerCase, UpperCase) VALUES ("abcd", "ABCD");

it will insert:

LowerCase | UpperCase
abcd      | ABCD

if you do

INSERT INTO MyTable (LowerCase, UpperCase) VALUES ("AbCd", "aBcD");

it will insert:

LowerCase | UpperCase
AbCd      | aBcD

it's up to you to sanitize the inputs to the case you want, or just let it go as entered.

however, when I do a

SELECT * FROM MyTable WHERE LowerCase="abcd";

it will return both entries.

Is data stored in tables case sensitive?

2 Answers2

On a side note:-