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.

Equality check in strings is usually case-insensitive in SQL. — ypercubeᵀᴹ, Jun 25 '12 at 16:54
What you want is a case sensitive collation. Read this: [Case Sensitivity in String Searches](http://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html) — ypercubeᵀᴹ, Jun 25 '12 at 16:55
possible duplicate of [MySQL case sensitive string comparison?](http://stackoverflow.com/questions/5629111/mysql-case-sensitive-string-comparison) — Marcus Adams, Jun 25 '12 at 17:15
For performance comparison, see http://stackoverflow.com/q/26455492/632951 — Pacerier, Oct 19 '14 at 21:27

Zane Bien · Accepted Answer · 2012-06-25T17:07:56.570

12

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.

edited Jun 25 '12 at 17:07

answered Jun 25 '12 at 16:55

Zane Bien

Hmm... my table collation is set to `latin1_swedish_ci`. I'm getting this error when running the above code sample `#1253 - COLLATION 'latin1_general_cs' is not valid for CHARACTER SET 'utf8'` – Oliver Spryn Jun 25 '12 at 17:02
Try `utf8_bin` collation then. – ypercubeᵀᴹ Jun 25 '12 at 17:04
Yes, I tried those, with no avail. Here is the SQL to create the table I am using `CREATE TABLE IF NOT EXISTS users ( /* Some columns, indexes, and primary keys */ ) ENGINE=MyISAM DEFAULT CHARSET=latin1;` – Oliver Spryn Jun 25 '12 at 17:08
There's no way you have latin1_swedish_ci for your UTF8 character set either. It's important to note the difference between LIKE and = here also. When using `=`, it ignores trailing spaces for the comparison, whereas LIKE doesn't ignore trailing spaces. LIKE is more strict and may be what you want. – Marcus Adams Jun 25 '12 at 17:10

score 2 · Answer 2 · edited Apr 06 '13 at 13:03

2

You can use STRCMP :

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

Note :

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

edited Apr 06 '13 at 13:03

Lukasz Czerwinski

answered Jun 25 '12 at 16:55

zessx

This cannot use any indices, though. – Emil Vikström Jun 25 '12 at 16:58
1

`SELECT STRCMP('John', 'john') ;` returns `0` in my machine. – ypercubeᵀᴹ Jun 25 '12 at 16:59
Right, just saw this function use the fields collates, then we still must specify `COLLATE utf8_general_cs` – zessx Jun 25 '12 at 17:02
`SELECT STRCMP('John', 'john' COLLATE utf8_bin);` happily returns `-1` :) And so does `SELECT STRCMP('John' COLLATE utf8_bin, 'john');` – ypercubeᵀᴹ Jun 25 '12 at 17:08
2

Ow, I found ! STRCMP **was** case sensitive (before MySQL 4.0) – zessx Jun 25 '12 at 17:11
Ah... that might explain why I was getting some wacky results. Thanks anyway! – Oliver Spryn Jun 25 '12 at 18:16

2 Answers2