Database charset and collation

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.

Suppose that we have an alphabet with four letters: 'A', 'B', 'a', 'b'. We give each letter a number: 'A' = 0, 'B' = 1, 'a' = 2, 'c' = 3. The letter 'A' is a symbol, the number 0 is the encoding for 'A', and the combination of all four letters and their encodings is a character set.

Now, suppose that we want to compare two string values, 'A' and 'B'. The simplest way to do this is to look at the encodings: 0 for 'A' and 1 for 'B'. Because 0 is less than 1, we say 'A' is less than 'B'. Now, what we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): "compare the encodings." We call this simplest of all possible collations a binary collation.

But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters 'a' and 'b' as equivalent to 'A' and 'B'; (2) then compare the encodings. We call this a case-insensitive collation. It's a little more complex than a binary collation.

In real life, most character sets have many characters: not just 'A' and 'B' but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules: not just case insensitivity but also accent insensitivity (an "accent" is a mark attached to a character as in German 'รถ') and multiple-character mappings (such as the rule that 'รถ' = 'OE' in one of the two German collations).

=========================================================================================
=========================================================================================

mysql> SELECT COLLATION_NAME,CHARACTER_SET_NAME FROM information_schema.COLLATIONS WHERE COLLATION_NAME like '%_cs';
+--------------------+--------------------+
| COLLATION_NAME     | CHARACTER_SET_NAME |
+--------------------+--------------------+
| latin1_general_cs  | latin1             |
| latin2_czech_cs    | latin2             |
| cp1250_czech_cs    | cp1250             |
| latin7_estonian_cs | latin7             |
| latin7_general_cs  | latin7             |
| cp1251_general_cs  | cp1251             |
+--------------------+--------------------+
6 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8                                          |
| character_set_connection | utf8                                          |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8                                          |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
| character_sets_dir       | /u01/app/mysql/product/5.7.10/share/charsets/ |
+--------------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%collat%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT COLLATION_NAME,CHARACTER_SET_NAME FROM information_schema.COLLATIONS WHERE COLLATION_NAME like 'utf_%_c%';
+--------------------------+--------------------+
| COLLATION_NAME           | CHARACTER_SET_NAME |
+--------------------------+--------------------+
| utf8_general_ci          | utf8               |
| utf8_unicode_ci          | utf8               |
...
...
...
| utf32_unicode_520_ci     | utf32              |
| utf32_vietnamese_ci      | utf32              |
+--------------------------+--------------------+
102 rows in set (0.00 sec)

mysql> SELECT COLLATION_NAME,CHARACTER_SET_NAME FROM information_schema.COLLATIONS WHERE COLLATION_NAME like 'utf_%_cs';
Empty set (0.00 sec)

mysql> SELECT COLLATION_NAME,CHARACTER_SET_NAME FROM information_schema.COLLATIONS WHERE COLLATION_NAME like 'utf8mb4_%';                                                                    +------------------------+--------------------+
| COLLATION_NAME         | CHARACTER_SET_NAME |
+------------------------+--------------------+
| utf8mb4_general_ci     | utf8mb4            |
| utf8mb4_bin            | utf8mb4            |
| utf8mb4_unicode_ci     | utf8mb4            |
..
..
| utf8mb4_vietnamese_ci  | utf8mb4            |
+------------------------+--------------------+
26 rows in set (0.00 sec)

=========================================================================================
=========================================================================================


Example:
--------
mysql> create table test1 (i varchar(30)) engine=innodb DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
Query OK, 0 rows affected (0.19 sec)
mysql> insert into test1 values ('Gaurav');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values ('GAURAV');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values ('gaurav');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `i` varchar(30) COLLATE latin1_general_cs DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test1;
+--------+
| i      |
+--------+
| Gaurav |
| GAURAV |
| gaurav |
+--------+
3 rows in set (0.00 sec)

mysql> select * from test1 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
+--------+
1 row in set (0.00 sec)

=========================================================================================
=========================================================================================

mysql> create table test2 (i varchar(30)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table test2;

+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `i` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test2 values ('Gaurav');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values ('GAURAV');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values ('gaurav');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test2;
+--------+
| i      |
+--------+
| Gaurav |
| GAURAV |
| gaurav |
+--------+
3 rows in set (0.00 sec)

mysql> select * from test2 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
| GAURAV |
| gaurav |
+--------+
3 rows in set (0.00 sec)

=========================================================================================
=========================================================================================

mysql> ALTER TABLE test2 CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_cs;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table test2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `i` varchar(30) COLLATE latin1_general_cs DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test2 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
+--------+
1 row in set (0.00 sec)

=========================================================================================
=========================================================================================

mysql> ALTER TABLE test2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test2 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test2 CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test2 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
| GAURAV |
| gaurav |
+--------+
3 rows in set (0.00 sec)

=========================================================================================
=========================================================================================

At last, i want to conclude session - before we start with new DB, make sure about its charset/collate.
Does it comes under - %cs (case-sensitive) OR %ci (case-in-sensitive)?
which charset need to use - latin1 or utf8?

References:
http://dev.mysql.com/doc/refman/5.7/en/charset-general.html