How to implement 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 use the VARBINARY data type, we first create a table. The query to create the 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)

Remember that UserId has data type VARBINARY(128) and Index('index_on_UserId2') on the "UserId" column.

_bin Sorting Rules

The second method is as follows. Let's 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 with data type varchar(128) and index(index_on_UserId) on "UserId" column.

Both of the above methods implement case-sensitive uniqueness and case-insensitive search in MySQL.

The above is the detailed content of How to implement case-sensitive uniqueness and case-insensitive search in MySQL?. For more information, please follow other related articles on the PHP Chinese website!