Monday, September 11, 2006

Case Sensitive MySQL

Today I was asked an interesting question: "Can we make MySQL to be case sensitive for SELECT queries and can we force a column in MySQL to be always lowercase?"

My response was that yes, we can have "instruct" MySQL to be case sensitive. One way to do that is to set the collation for the table (or column) to be either binary or case sensitive as shown below.

The naming convention for collation in mysql is as follows:

*_bin: represents binary case sensitive collation
*_cs: case sensitive collation
*_ci: case insensitive collation

###########
# Start binary collation example
###########
mysql> create table case_bin_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO case_bin_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM case_bin_test WHERE word LIKE 'f%';
+---------+
| word |
+---------+
| froogle |
| flickr |
+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM case_bin_test WHERE word LIKE 'F%';
+---------+
| word |
+---------+
| Frank |
| FlicKr |
+---------+
4 rows in set (0.00 sec)

###########
# End
###########


Here's another way

###########
# Start case sensitive collation example
###########

mysql> create table case_cs_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_general_cs;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO case_cs_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM case_cs_test WHERE word LIKE 'F%';
+---------+
| word |
+---------+
| Frank |
| FlicKr |
+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM case_cs_test WHERE word LIKE 'f%';
+---------+
| word |
+---------+
| froogle |
| flickr |
+---------+
2 rows in set (0.00 sec)

###########
# end
###########


Yet another way is to specify the collation during query in case the collation cannot be specified for the entire table. Following are a few different ways of specifying this.


mysql> create table case_test (word VARCHAR(10)) CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO case_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM case_test WHERE word LIKE 'f%';
+---------+
| word |
+---------+
| Frank |
| froogle |
| flickr |
| FlicKr |
+---------+
6 rows in set (0.01 sec)

mysql> SELECT * FROM case_test WHERE word LIKE 'F%';
+---------+
| word |
+---------+
| Frank |
| froogle |
| flickr |
| FlicKr |
+---------+
6 rows in set (0.01 sec)


mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'F%';
+---------+
| word |
+---------+
| Frank |
| FlicKr |
+---------+
4 rows in set (0.05 sec)

mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'f%';
+---------+
| word |
+---------+
| froogle |
| flickr |
+---------+
2 rows in set (0.00 sec)



mysql> SELECT * FROM case_test WHERE word LIKE 'f%' COLLATE latin1_bin;
+---------+
| word |
+---------+
| froogle |
| flickr |
+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM case_test WHERE word LIKE 'F%' COLLATE latin1_bin;
+---------+
| word |
+---------+
| Frank |
| FlicKr |
+---------+
4 rows in set (0.01 sec)


mysql> SELECT * FROM case_test WHERE word LIKE 'F%' COLLATE latin1_general_cs;
+---------+
| word |
+---------+
| Frank |
| FlicKr |
+---------+
4 rows in set (0.04 sec)



Now regarding "forcing" a column to always be lower case.

Unfortunately, we cannot do that in MySQL 4.x, other than converting a value to lower case before inserting at the application side.

Starting with MySQL 5, however we can use triggers as the following example shows:


mysql> create table case_test (word VARCHAR(10)) CHARACTER SET latin1;
Query OK, 0 rows affected (0.05 sec)
mysql> DELIMITER //
mysql> CREATE TRIGGER lowercased BEFORE INSERT ON case_test FOR EACH ROW BEGIN SET NEW.word=LOWER(NEW.word);
END;//
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO case_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM case_test;
+---------+
| word |
+---------+
| frank |
| google |
| froogle |
| flickr |
| flickr |
+---------+
7 rows in set (0.00 sec)


I am interested in hearing about other approaches to achieving the same results.

21 comments:

Stephen Gornick said...

> Can we make MySQL to be case sensitive for SELECT queries

Wouldn't a simple SELECT BINARY do the same thing?
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

Anonymous said...

Hello,
I have a problem regarding case sensitivity and I'd like to have your advice.
Mysql odbc driver has problems handling latin1_bin collations set for case-sensitivity purposes.

So for select queries on latin1_bin collated columns I tried one of your solutions (general or query change) and it solved my problem.

But I still have a case for which I have no solution : when your run a "SHOW CREATE TABLE" query the table creation instruction is returned as latin1_bin and for me there is no way to get it in text, with a general setting or query paramater...
Do you have any idea ?
Thanks and by the way bravo for your useful blog.

Zumba
(beta AT the-continent.org)

ps : sorry for my english, I'm french !

Anonymous said...

Hi,

I always have to use correct case in queries. Like for table MyTable...

select * from MyTable
works fine but...
select * from mytable
doesn't work.

How do I fix that? (I'm not an expert and really appreciate the help).

Anonymous said...

Thanks man,

useful blog.

-sunil

Anonymous said...

Fine one...
Thanks

Artem Russakovskii said...

Both of these approaches are good, though triggers are the only way I can think of to enforce lower case.

Only problem I see with your approach is it only affects inserts. Updates to the data will allow upper case. Besides creating a duplicate trigger that fires on update, do you see another way (multiple firing conditions)?

Unknown said...

Is my sql is case-sensitive? I make some scrips in winrunner and i have all columns and table names in lowercase but that is uppercase inthe database. so will winrunner will retrieve table and columns written in lowercase inthe script from the my sql where they all in uppercase

thetigergo said...

Can we make mysql proper casing in Windows? I have a database in Kubuntu Linux. I use a proper case table name. Example: MyTable. When I backup it and restore in Windows MySQL. It is converted to mytable. I encountered that in Linux it is case sensitive in MySQL Query Browser. It will trigger an error when I use mytable instead of MyTable. What I want is, when I backup my MySQL in windows becuase it has something I changed there. And I restore my backup to Linux MySQL, mytable is a case. Not MyTable. Now my program in java will trigger an error. How can I solve this problem?

Anonymous said...

Hi,

This is very useful info, thanks.

varun said...

thanx...i got the solution from this blog...

varun shah
9898476750

Anonymous said...

Thanks. Solved my problem nice and easy.

Mandar said...

TY GOOD 1 :) I liked it :)

Prashant Verma said...

Thanks a lot, this have really helped

Prashant Verma said...

Thanks a lot, this has really helped

Unknown said...

Thanks, it's work

Supreet said...

Thanks a lot. But I found that "WHERE BINARY [COLUMN _NAME]='something'" also does the same thing and is also easier to remember

http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

Anonymous said...

If you have a collation declared in your database/table/column that does not provide indifference with case-sensitivity. Hence when you do a query like
SELECT userID
FROM user WHERE userID LIKE '%a%';
Gives you only results of userID with a (lower case)
You can fix this to get a (lower case) and A (upper case) adding the following:
SELECT userID
FROM user
WHERE userID COLLATE latin1_swedish_ci LIKE '%a%';

blOOr said...

TANKS A MILLION !

Anonymous said...

thank you man!

Unknown said...

I'm having the same as Zumba.

The database was created using "DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci" in windows.

All right.. but when I call a SHOW CREATE TABLE I'm get some parts of constrants in lowercase..

When I did the same in linux server, all go fine.

Some idea?

Anonymous said...

Thank you vey much. This fixed my problem of replacing key-sensitive strings in my tables (I am using utf8 instead of latin1).