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

MySQLMySQLi Database

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.

raja
Published on 19-Mar-2019 12:34:12
Advertisements