MySQL varchar case sensitive/insensitive

Source: https://dev.mysql.com/doc/refman/5.6/en/charset-collation-information-schema.html
https://www.stetsenko.net/2012/10/mysql-unique-case-sensitive-varchar/
https://stackoverflow.com/questions/18737805/mysql-case-sensitive-in-utf8-general-ci
https://stackoverflow.com/questions/10929836/utf8-bin-vs-utf-unicode-ci
https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

String columns in INFORMATION_SCHEMA tables have a collation of utf8_general_ci, which is case insensitive. However, for values that correspond to objects that are represented in the file system, such as databases and tables, searches in INFORMATION_SCHEMAstring columns can be case-sensitive or insensitive, depending on the characteristics of the underlying file system and the value of the lower_case_table_names system variable.

MySQL is case insensitive by default and normally it is more than enough. However one of my recent projects required a case sensitive varchar column with unique index. Latter would immediately trigger ‘Duplicate entry … for key …’ error for “the same” strings.

CREATE TABLE file ( id INT AUTO_INCREMENT, name VARCHAR(100) NOT NULL, PRIMARY KEY(id), UNIQUE(name) );   INSERT INTO file (name) VALUES ('test.txt'), ('test.TXT');

For example in the query above the second insert fails returning that error.

A way to address this is just to use a case sensitive collation (e.g. utf8_bin).

CREATE TABLE file ( id INT AUTO_INCREMENT, name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY(id), UNIQUE(name) );