0

I'm wondering the advantage of doing

SELECT * FROM table WHERE BINARY name='myname'

And having the VARCHAR coalition as latin_swedish_ci compared to having the coalition as a case sensitive one like latin_general_cs

beenjaminnn
  • 752
  • 1
  • 11
  • 23
  • See [Character Sets and Collations in General](http://dev.mysql.com/doc/en/charset-general.html) – eggyal Jan 10 '14 at 21:21
  • Is using the binary operator preferred to using a coalition that is case sensitive then? – beenjaminnn Jan 10 '14 at 22:14
  • It depends on what you are comparing, and what results you expect. Generally speaking, textual data demands a collation for comparisons to behave correctly. – eggyal Jan 10 '14 at 22:16
  • I want to compare varchar usernames and varchar fields that were populated with PASSWORD('string') – beenjaminnn Jan 10 '14 at 22:18
  • Firstly, take heed of the **Note** under the documentation for the [`PASSWORD()`](http://dev.mysql.com/doc/en/encryption-functions.html#function_password): "you should *not* use it in your own applications ... Statements that invoke PASSWORD() may be recorded in server logs or in a history file" etc. – eggyal Jan 10 '14 at 22:23
  • Secondly, the result of `PASSWORD()` is a text string and should technically be compared using an appropriate collation, albeit perhaps something as mundane as `latin1_bin`. If you instead stored the *binary* value represented by that string, i.e. the result of applying `UNHEX()` to it, it would then be appropriate to perform `BINARY` comparisons (and indeed to store the value in a binary string type column). – eggyal Jan 10 '14 at 22:24
  • Similarly, the username is a text string and should also be compared using an appropriate collation - although if you require that usernames be typed exactly as before, with the same lettercase and accent variations etc, it may well be that something like `latin1_bin` is also the most suitable here too. Personally I'd apply the Robustness Principle and permit users to type their username in different ways that seem logical to them - i.e. apply the collation relevant to their locale. – eggyal Jan 10 '14 at 22:26
  • Wow, thanks for the heads up. I'm assuming that I should use something like [this](http://stackoverflow.com/questions/14798275/best-way-to-store-passwords-in-mysql-database) for passwords? – beenjaminnn Jan 10 '14 at 22:29
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/44985/discussion-between-beenjaminnn-and-eggyal) – beenjaminnn Jan 10 '14 at 22:31

0 Answers0