How to to Set the Collation for the Table (or column) to be either Binary or Case-Sensitive?

more images

First thing first, you need to check out with the available collations in the system. SHOW COLLATION can do that for you. This statement lists collations supported by the server. By default, the output from SHOW COLLATION includes all available collations. The LIKE clause, if present, indicates which collation names to match. The WHERE clause can be given to select rows using more general conditions.

Eg.

SHOW COLLATION
[LIKE 'pattern' | WHERE expr]

————————————————————————————————————————————————————
————————————————————————————————————————————————————

mysql> SHOW COLLATION LIKE ‘latin1%’;

+——————-+———+—-+———+———-+———+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+——————-+———+—-+———+———-+———+
| latin1_german1_ci | latin1 | 5 | | | 0 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 |
| latin1_danish_ci | latin1 | 15 | | | 0 |
| latin1_german2_ci | latin1 | 31 | | Yes | 2 |
| latin1_bin | latin1 | 47 | | Yes | 0 |
| latin1_general_ci | latin1 | 48 | | | 0 |
| latin1_general_cs | latin1 | 49 | | | 0 |
| latin1_spanish_ci | latin1 | 94 | | | 0 |
+——————-+———+—-+———+———-+———+

Further, look at the details here, 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 (’Avik’),(’Google’),(’angsuman’),(’Amit’),(’arim’);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM case_bin_test WHERE word LIKE ‘a%’;
+———+
| word |
+———+
| angsuman |
| arim |
+———+
2 rows in set (0.00 sec)

mysql> SELECT * FROM case_bin_test WHERE word LIKE ‘A%’;
+———+
| word |
+———+
| Avik |
| Amit |
+———+
4 rows in set (0.00 sec)

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

———————————————————————————————————————————————————–
———————————————————————————————————————————————————–

Here’s another way-out,

###########
# 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 (’Avik’),(’Google’),(’angsuman’),(’Amit’),(’arim’);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM case_cs_test WHERE word LIKE ‘A%’;
+———+
| word |
+———+
| Avik |
| Amit |
+———+
4 rows in set (0.00 sec)

mysql> SELECT * FROM case_cs_test WHERE word LIKE ‘a%’;
+———+
| word |
+———+
| angsuman |
| arim |
+———+
2 rows in set (0.00 sec)

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

————————————————————————————————————————————————-
————————————————————————————————————————————————-

This is the third and final example 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 (’Avik’),(’Google’),(’angsuman’),(’Amit’),(’arim’);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM case_test WHERE word LIKE ‘a%’;
+———+
| word |
+———+
| angsuman |
| Amit|
| arim |
| Avik |
+———+
6 rows in set (0.01 sec)

mysql> SELECT * FROM case_test WHERE word LIKE ‘A%’;
+———+
| word |
+———+
| angsuman |
| Amit|
| arim |
| Avik |
+———+
6 rows in set (0.01 sec)

mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE ‘F%’;
+———+
| word |
+———+
| Avik |
| Amit |
+———+
4 rows in set (0.05 sec)

mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE ‘a%’;
+———+
| word |
+———+
| angsuman |
| arim |
+———+
2 rows in set (0.00 sec)

mysql> SELECT * FROM case_test WHERE word LIKE ‘a%’ COLLATE latin1_bin;
+———+
| word |
+———+
| angsuman |
| arim |
+———+
2 rows in set (0.00 sec)

mysql> SELECT * FROM case_test WHERE word LIKE ‘A%’ COLLATE latin1_bin;
+———+
| word |
+———+
| Avik |
| Amit |
+———+
4 rows in set (0.01 sec)

mysql> SELECT * FROM case_test WHERE word LIKE ‘A%’ COLLATE latin1_general_cs;
+———+
| word |
+———+
| Avik |
| Amit |
+———+
4 rows in set (0.04 sec)

So, hope I could present you with some useful information. If you find something more do share with us.