SQL unique varchar case sensitivity question
Solution 1
Looks like mysql is case insensitive by default:
You probably need to create the column with a case sensitive collation (e.g. utf8_bin):
CREATE TABLE WORDS (
ID BIGINT AUTO_INCREMENT,
WORD VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE,
PRIMARY KEY(ID)
);
Solution 2
By default MySQL ignores differences in case and trailing spaces on varchar
.
If you need it to be case sensitive, you can alter the table to be varchar(...) binary
.
Use show create table
to better understand how MySQL converts this to full notation.
If you need to pay attention to trailing spaces as well as be case sensitive, use varbinary
instead of varchar
.
Solution 3
Try this:
ALTER TABLE WORDS CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Seth
Updated on December 01, 2020Comments
-
Seth over 3 years
I'm trying to populate a SQL table with a list of words. The table itself it pretty simple:
CREATE TABLE WORDS( ID BIGINT AUTO_INCREMENT, WORD VARCHAR(128) NOT NULL UNIQUE, PRIMARY KEY(ID) );
The problem I'm running into is this: when I do the following inserts back to back
INSERT INTO WORDS(WORD) VALUES('Seth'); INSERT INTO WORDS(WORD) VALUES('seth');
The second insert fails with a constraint violation ("Duplicate entry 'seth' for key 'WORD'").
How can I get the
UNIQUE
constraint onWORD
to be case sensitive? -
Seth almost 13 yearsThat did it. Thanks. For anyone finding this question later, I had to use the 'utf8_bin' collation because mysql didn't have 'utf8_cs'.
-
groovenectar about 12 yearsThanks @Seth, that was helpful along with Bill's info, utf8_bin applied to my situation as well
-
feedc0de almost 10 years#1273 - Unknown collation: 'utf8_cs'
-
Josef Sábl almost 10 yearsHuge disappointment with mysql :-( "Seth" and "seth" are NOT same strings! Thanks for helping out.
-
Lauris Kuznecovs about 8 yearsUse utf8_bin in case of utf8_cs
-
stamster about 7 yearsI have collation set as
utf8_bin
. And still varchar(50)FFAB213D-810C-74BD-1B34-E93FC473CF6A
is the same asffab213D-810C-74BD-1B34-E93FC473CF6A
(MariaDB 10) -
Xhua about 5 yearsFor utf8mb4, you can use
SET utf8mb4 COLLATE utf8mb4_bin
instead. -
Surajit Biswas over 3 yearsMany Thanks man, my clients were about to file case on me. Use this jol.ink