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.

Updated on: 30-Jul-2019

893 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements