Home > MySQL > Why MySQL gave case insensitive string results

Why MySQL gave case insensitive string results

Problem

Today MySQL was giving me crappy results… when I searched for a user with name ‘GUEST’ it gave me a user with name ‘Guest’. Which is close… but not close enough. Somehow MySQL handled my input in a case insensitive way.

During my journey across the internet looking for answers, I found that the case sensitivity of table names depends on your platform because MySQL uses files to store tables. I also found many users asking how to do case insensitive queries… but nobody answering *my* question.

Solution 1

After I gave up on finding an answer online I started browsing around in MySQL Administrator, and noticed a “Binary” checkbox. After checking this box for the column Name and executing the changes, MySQL was suddenly case sensitive….

Solution 2

Then I came across this page, which made me fiddle around some more with the collation. I discovered that the problem was solved after selecting a binary collation on the Name column. Because I couldn’t find a way to set this property though the ORM tool I’m using (JPA, Toplink) I configured the MySQL server to use a binary collation method. This was a good moment to switch from the idiotic “cp1252 West European” charset (Why do those still exist???) to Unicode (UTF8).

This is what /etc/my.cnf looks like on my System (MySQL 5.0.45):

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
default-storage-engine=innodb // myisam doesn’t support foreign keys
default-character-set=utf8
default-collation=utf8_bin

If anyone can explain this behaviour, and why MySQL doesn’t do this by default, please let me know!

Important showstopper: Completely drop any database or schema that was created when the server was using a non-Unicode charset. Just dropping and recreating the tables is NOT enough. (Took me another hour to figure that out… )

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: