MySQL Is Strictly Equal to Operator?

Question

In many programming languages there is the basic equals operator which will see if the text of two strings are equal:

if ("Hi" == "hi") {
  //this code runs
}

But there is also the strict equal to operator:

if ("Hi" === "hi") {
  //this code will never run
}

Is there an equivalent operator for the above code snippet in MySQL? The = operator just doesn't cut it. If I run:

SELECT * FROM users WHERE name = 'john';

MySQL will return rows with a John, with a capital "J".

Thank you for your time.

Answer 1

You can use the COLLATE operator to convert the column to a case-sensitive collation:

SELECT * FROM users WHERE name LIKE 'john' COLLATE utf8_bin

MySQL documentation on case sensitivity.

Answer 2

You can use STRCMP :

SELECT * FROM users WHERE STRCMP(name, 'john') = 0;

Note :

• 0 = same
• -1 = first element smaller
• 1 = first element bigger

You must use COLLATES if you're using MySQL 4.0+ (STRCMP is no longer case sensitive) :

• COLLATE utf8_general_ci = case insensitive (ci)
• COLLATE utf8_general_cs = case sensitive (cs)