0

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!

user10398534
  • 145
  • 14

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

This works without the single quotes around cloumn

SELECT * FROM table1 WHERE `column` LIKE 'data' COLLATE latin1_general_ci;

As you asked the collation has to fir to the character set, so if you have like me utf8mb5, you write:

SELECT * FROM table1 WHERE `column` LIKE _latin1 'data'   COLLATE latin1_general_ci;
nbk
  • 45,398
  • 8
  • 30
  • 47
  • I have the same two errors when using this. Perhaps it is just my server configuration that does not support it. – user10398534 Apr 21 '20 at 20:26
  • which version do you have mariadb 10.3 and 10.4 works just fine mysql also in version 8 which i tested, so be more accurate – nbk Apr 21 '20 at 20:42
  • there is also the problem that your selected collation don't work with utf8mb4, so maybe this could also be aproblem, but you where very sparse with information – nbk Apr 21 '20 at 20:50
  • As mentioned in my question, I am using 10.3.22-MariaDB server and libmysql - 5.6.43 as a database client – user10398534 Apr 21 '20 at 20:56
  • I added how you would do it with a utf8mb4 database/table/column, which doesn't know latin_general_ci, you have always to check for it. this works under evry database in mysql/mariadb. If you still get errors you have to provide a fiddle example so that i can check – nbk Apr 21 '20 at 21:08