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, Jan 19 '13 at 14:00

score 94 · Accepted Answer · edited Jun 18 '21 at 12:59

94

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

edited Jun 18 '21 at 12:59

Yves M.

answered Jan 21 '09 at 01:13

Eran Galperin

Hemerson Varela · Answer 2 · 2018-03-14T13:34:46.943

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

2 Answers2