Tuesday, August 25, 2009

[Level 1] Solve MySQL case insensitive problem.

In default, MySQL is case insensitive.
ex.
mysql> select * from t where n = 'a';
+------+
| n    |
+------+
| A    |
+------+


If we want to solve this problem, we can use following ways:

1. Modify you SQL statement(temporary solution), add the key word 'binary' before your string.
ex.
mysql> select * from t where n = binary 'a';
Empty set (0.00 sec)

mysql> select * from t where n = binary 'A';
+------+
| n    |
+------+
| A    |
+------+
1 row in set (0.00 sec)

2. Modify the table schema, and let the column be case sensitive.
mysql> select * from t where n = 'a';
Empty set (0.00 sec)

mysql> select * from t where n = 'A';
+------+
| n    |
+------+
| A    |
+------+
1 row in set (0.00 sec)

3. To let the new table has case sensitive feature, modifing the database character and collation. (If you want the new table with case insensitive, you can assign the table with "not" case sensitive)
mysql> alter database test character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.01 sec)
mysql> create table tt ( n varchar(32));
Query OK, 0 rows affected (0.32 sec)
mysql> select * from tt where n='A';
Empty set (0.00 sec)

mysql> select * from tt where n='a';
+------+
| n    |
+------+
| a    |
+------+
1 row in set (0.00 sec)


Wish this helps.

regards,
Stanley Huang