My SQL Table Names are Case Sensitive in UNIX

I looked at case sensitivity in MySQL database names in a couple of earlier posts. Type case sensitivity in the SEARCH THIS BLOG box to the right and click on Search to see them if you are interested. I recently discovered that table names are case sensitive on UNIX too: 

mysql> create table andrew (col1 varchar(10));

Query OK, 0 rows affected (0.03 sec)

 

mysql> select * from andrew;

Empty set (0.00 sec)

 

mysql> select * from ANDREW;

ERROR 1146 (42S02): Table 'test.ANDREW' doesn't exist

mysql>

Creating a MySQL Database on UNIX

In the previous example, I created a MySQL database on Windows and found that database names are not case sensitive.

I ran the commands below on a UNIX machine and, as you might expect, MySQL database names ARE case sensitive there:

mysql> create database Andrew;

Query OK, 1 row affected (0.01 sec)

 

mysql> drop database andrew;

ERROR 1008 (HY000): Can't drop database 'andrew'; database doesn't exist

mysql> drop database Andrew;

Query OK, 0 rows affected (0.14 sec)

 

mysql>

Creating a MySQL Database on Windows XP

In the example below, the show databases command is used to display the names of databases which already exist. The create database command is then used to create a database called andrew. The show databases command is run again and database andrew appears in the list. Next, the drop database command is used to drop a database called ANDREW, showing that in Windows, MySQL database names are not case sensitive. Finally, the show databases command is run again to show that the database has gone:
  

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.05 sec)

mysql> create database andrew;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| andrew             |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
7 rows in set (0.01 sec)

mysql> drop database ANDREW;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql>