MySQL: unique case sensitive varchar

Posted October 21, 2012 * Comments(3)

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)
);

3 Comments so far

  1. Andrew on November 21st, 2012

    Exactly what I was looking for! Another search result mentions utf8_cs, which did not work with my version of msyql

  2. Scott on September 2nd, 2015

    Perfect, this worked for me where changing the table collation did not! Thanks

  3. Michael Thomas on January 23rd, 2019

    I ran into this same problem, thanks for the fix!

Leave a reply