204

This has been asked on this site before but I couldn't find a sufficient answer. If I'm doing a query like:

Select Seller from Table where Location = 'San Jose'

How can I make it return only Sellers with Location 'San Jose' instead of 'san jose' or something else?

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
Michael Liao
  • 2,623
  • 3
  • 17
  • 10

3 Answers3

468

MySQL queries are not case-sensitive by default. Following is a simple query that is looking for 'value'. However it will return 'VALUE', 'value', 'VaLuE', etc…

SELECT * FROM `table` WHERE `column` = 'value'

The good news is that if you need to make a case-sensitive query, it is very easy to do using the BINARY operator, which forces a byte by byte comparison:

SELECT * FROM `table` WHERE BINARY `column` = 'value'
hongsy
  • 1,498
  • 1
  • 27
  • 39
James mason
  • 4,681
  • 1
  • 12
  • 3
  • 3
    1) using `binary` for this purpose is risky because it is literally going to compare the bytes of the two strings. If your default server/session charset does not match the charset of the column then accent characters will be considered not equal (i.e. ü in latin1 is `0xFC` where as in utf8mb4 it is `0xC3Bc`). 2) As Shenxian points out below, you should apply your conversion to the parameter rather than the column. It will have to same functional effect but it will allow the query to use an index if one is present on your column. – Paul Wheeler May 23 '19 at 21:48
121

To improve James' excellent answer:

It's better to put BINARY in front of the constant instead:

SELECT * FROM `table` WHERE `column` = BINARY 'value'

Putting BINARY in front of column will prevent the use of any index on that column.

Yves M.
  • 29,855
  • 23
  • 108
  • 144
Shenxian
  • 1,224
  • 1
  • 9
  • 5
33

Whilst the listed answer is correct, may I suggest that if your column is to hold case sensitive strings you read the documentation and alter your table definition accordingly.

In my case this amounted to defining my column as:

`tag` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''

This is in my opinion preferential to adjusting your queries.

Mark Amery
  • 143,130
  • 81
  • 406
  • 459
Thomas Clowes
  • 4,529
  • 8
  • 41
  • 73
  • 3
    I've edited to fix what I'm pretty sure was a silly typo that was precisely the opposite of what you wanted to say. I feel like I ought to notify you, though (and since it's a two-character change the site won't notify you automatically). Assuming you're happy with the change, feel free to just flag my comment as obsolete and let it be nuked. (And if I've screwed up, shout at me.) – Mark Amery Sep 26 '16 at 13:50
  • For more recent versions of MySql you should replace the deprecated `utf8` with `utf8mb4` – Paul Wheeler May 23 '19 at 21:54