Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
828 Views
Advertisements