Menu
Are indices (indexes) defined as UNIQUE case sensitive in MySQL?
209
The default collations used by SQL Server and MySQL do not distinguish between upper and lower case letters—they are case-insensitive by default.
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.
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
In MySQL, UNIQUE INDEX is used to define multiple non-duplicate columns at once. While PRIMARY KEY constraint also assures non-duplicate values in a column, only one PRIMARY KEY can be defined per table. So for scenarios where multiple columns are to be made distinct, UNIQUE INDEX is used.
It depends on the collation of the field - if it's ci (case insensitive) or cs (case sensitive). The unique index would apply accordingly.
158
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
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
