I learned something new today regarding MySQL.

The use of binary operator if used in a query or as collation type in DDL can make some significant difference in the results. Generally, when we create some table in MySQL, we don’t use binary collation. For example:

CREATE TABLE students (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
)ENGINE=MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Now Lets insert some data

INSERT INTO stduents set name = 'Prajwal Tuladhar';
INSERT INTO stduents set name = 'PrAJwal TUladhar';

If a query is run in the above table:

mysql> SELECT *
    -> FROM students
    -> WHERE name = 'Prajwal Tuladhar';
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Prajwal Tuladhar |
|  2 | PrAJwal TUladhar |
+----+------------------+
2 rows in set (0.00 sec)

So, if we want case sensitive results from a case insensitive table as above:

mysql> SELECT *
    -> FROM students
    -&gt; WHERE <strong>binary</strong> name = 'Prajwal Tuladhar';
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Prajwal Tuladhar |
+----+------------------+
1 row in set (0.00 sec)

Or we can just changed the collation of the table as:

ALTER TABLE `students`  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

More information about binary operator here.

Very good documentary series by BBC regarding history of gold.

• Part I
• Part II
• Part III

One of the greatest obsessions of human beings.