Search code examples
mysqlsqlmariadbcase-sensitivelibmysql

Case-sensitive string comparison in SQL (MariaDB, MySQL)

I have read many questions and answers on StackOverflow (posted years ago) concerning this issue, but none that I found have worked using a 10.3.22-MariaDB server and libmysql - 5.6.43 as database client.

I am trying to do the following: SELECT * FROM table WHERE 'column' = 'data' LIMIT 1
The command above, however, will select the row both if the value of column is DaTa or data and I am endeavouring to go for the latter only.

Answers to similar questions from 2011 (MySQL) suggest:

SELECT * FROM table WHERE BINARY 'column' = 'data'
SELECT * FROM table WHERE 'column' = BINARY 'data'
SELECT * FROM table WHERE 'column' LIKE BINARY 'data'
SELECT * FROM table WHERE 'column' LIKE 'data' COLLATE latin1_general_cs

None of the commands above respected the case of the letters. Moreover, the last one did not even run due to

  1. Unrecognized keyword. (near "COLLATE" at position 45)
  2. Unexpected token. (near "latin1_general_cs" at position 53)

What is the solution to this problem in 2020? What am I doing wrong or what is wrong with my server?
Any help would be highly appreciated!

Solution

  • This should work:

    WHERE BINARY column = 'data'

    However, it will not work if you put column in single quotes. That would be a string as opposed to a column reference.