Are UNIQUE indices case sensitive in MySQL?

Question

Are indices (indexes) defined as UNIQUE case sensitive in MySQL?

As per popular demand, maybe you should update the chosen answer. — Camilo Martin
– Camilo Martin, Commented Jan 19, 2013 at 14:00

Yves M. · Accepted Answer · 2021-06-18 12:59:07Z

100

It depends on the collation of the field - if it's ci (case insensitive) or cs (case sensitive). The unique index would apply accordingly.

Yves M.

31.3k24 gold badges111 silver badges154 bronze badges

answered Jan 21, 2009 at 1:13

Eran Galperin

86.9k25 gold badges119 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hemerson Varela · Accepted Answer · 2018-03-14 13:34:46Z

27

You can make a column case-sensitive by using this syntaxis. the unique index also will be case-sensitive.

ALTER TABLE tbl_name MODIFY
col_name column_definition
[CHARACTER SET charset_name]
[COLLATE collation_name]

Example:

ALTER TABLE `tablename` MODIFY
`column` VARCHAR(100) 
CHARACTER SET utf8
COLLATE utf8_bin;

Note: utf8_bin compares strings by the binary value of each character in the string.

Tested on Msql 5.5.X

answered Jun 28, 2013 at 16:19

Hemerson Varela

26.1k18 gold badges73 silver badges69 bronze badges