MySQL case sensitivity for primary key

Question

Are MySQL primary key values case sensitive? If it's an option how do I set it? I want the table to be able to store "www.Example.com" and "www.example.com" as different values.

Tudela

What happened when you tried? – Euripides

Well its giving me a duplicate primary key error but I am inserting into the table multiple times in one query. So I believe that its sees the two entries as the same. This is what I need to know how to change. – Tudela

Answer 1

You can set per-column collations in MySQL: https://dev.mysql.com/doc/refman/5.5/en/charset-column.html

e.g. if your table is generally (say) case insensitive, you can override it per-field to be case-sensitive.

Bachelor

So do I understand it correctly that "utf8_general_ci" would most likely mean that it is case insensitive? if thats true I should use a binary collation? – Tudela

yes. ci is case insensitive. but there's utf8_general_cs for case sensitive. – Bachelor

I am getting Unknown collation: 'utf8_general_cs'. Is this an issue with the version of MySQL I am using? (5.1) – Jimmiejimmy

As per serverfault.com/a/137466 the simplest solution is to use utf8_bin for case sensitivy. – Someday

Answer 2

The BINARY keyword will do the trick, though I'm not sure if it's the recommended way to do this:

CREATE TABLE testpk (MyPK VARCHAR(20) BINARY PRIMARY KEY NOT NULL)

Crystallography

Answer 3

MySQL doesn't have case-sensitive Unicode collations, ... So the simplest trick you have is using collation utf8_bin.

Waspish