How to achieve case sensitive uniqueness and case insensitive search in MySQL?

You can achieve case sensitive uniqueness and case insensitive search with the help of the following two ways −

• VARBINARY data type
• _bin collation

VARBINARY data type

To work with the VARBINARY data type, let us first create a table. The query to create a table is as follows −

mysql> create table SearchingDemo2
   -> (
   -> UserId VARBINARY(128) NOT NULL,
   -> UNIQUE KEY index_on_UserId2(UserId )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected, 1 warning (0.99 sec)

Keep in mind UserId has data type VARBINARY(128) and Index(‘index_on_UserId2’) on a column ‘UserId’.

_bin collation

The second way is as follows. Let us create a new table −

mysql> create table SearchingDemo
   -> (
   -> UserId varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
   -> UNIQUE KEY index_on_UserId(UserId )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected, 2 warnings (0.88 sec)

UserId has data type varchar(128) and index(index_on_UserId) on a column ‘UserId’.

Both the above approach achieve case sensitive uniqueness and case insensitive search in MySQL.