Can I make mysql table columns case insensitive?

Question

I am new to mysql (and sql in general) and am trying to see if I can make data inserted into a column in a table case insensitive.

I am storing data like state names, city names, etc. So I want to have a unique constraint on these types of data and on top of that make them case insensitive so that I can rely on the uniqueness constraint.

Does mysql support a case-insensitive option on either the column during table creation or alternatively when setting the uniqueness constraint on the column? What is the usual way to deal with such issues? I would appreciate any alternate ideas/suggestions to deal with this.

EDIT: As suggested, does COLLATE I think only applies to queries on the inserted data. But to really take advantage of the uniqueness contraint, I want to have a case insensitivity restriction on INSERT. For e.g. I want mysql to not allow insertions of California and california and cALifornia as they should be the same. But if I understand the uniqueness constraint prooperly, having it on the StateName will still allow the above four inserts.

Answer 1

By default, MySQL is case-insensitive.

CREATE TABLE test
(
    name VARCHAR(20),
    UNIQUE(name)
);

mysql>     INSERT INTO test VALUES('California');
Query OK, 1 row affected (0.00 sec)

mysql>     INSERT INTO test VALUES('california');
ERROR 1062 (23000): Duplicate entry 'california' for key 'name'

mysql>     INSERT INTO test VALUES('cAlifornia');
ERROR 1062 (23000): Duplicate entry 'cAlifornia' for key 'name'

mysql>     INSERT INTO test VALUES('cALifornia');
ERROR 1062 (23000): Duplicate entry 'cALifornia' for key 'name'

mysql> SELECT * FROM test;
+------------+
| name       |
+------------+
| California |
+------------+
1 row in set (0.00 sec)

Use BINARY when you need case-sensitivity

To make case-sensitive in MySQL, BINARY keyword is used as follows

mysql>     CREATE TABLE test
    ->     (
    ->         name varchar(20) BINARY,
    ->         UNIQUE(name)
    ->     );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>     INSERT INTO test VALUES('California');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>     INSERT INTO test VALUES('california');
Query OK, 1 row affected (0.00 sec)

mysql>     INSERT INTO test VALUES('cAlifornia');
Query OK, 1 row affected (0.00 sec)

mysql>     INSERT INTO test VALUES('cALifornia');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>     SELECT * FROM test;
+------------+
| name       |
+------------+
| California |
| cALifornia |
| cAlifornia |
| california |
+------------+
4 rows in set (0.00 sec)

Answer 2

You can use COLLATE operator: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html