It is a mistake to use BINARY for this case if you are storing passwords. Incidentally, passwords should not be stored in DB .

BINARY completely ignores unicode and equivalence of characters, so a% composite% will always be treated other than a Á of single character.

In summary: it may happen that, depending on the OS and the application, a user type the same character, and it is sent in different ways, invalidating a legitimate access, and the user will not even know what happened. p>

It's really cool to store hashes , on the other hand. Hashes are naturally "binary" in the sense that they can use any character from Á to 0x00 . I've seen a lot of people storing the hashes in hex , but storing them directly in binary you spend half the space.

In names, even, nor would be right to be CS, except some very specific restriction of the application. Having two different users named "Joseph" and "Joseph" is a good principle of confusion.

Collations

The solution in these cases is to use the correct collations in each bank column , as needed.

You can use it natively by specifying the table creation (or changing it), or you can use verb 0xff of the DB at the time of the query.

For example, in unicode, you have COLLATE , which considers the character code in unicode , not the individual bytes . It is worth noting that when displaying on the screen, it may be interesting to force a utf8_bin to keep the alphabetical order expected, whether to actually use this in the names.

In ORDER BY column COLLATE utf8_general_ci , you have the endings latin for _case_insensitive_ and _ci for sensitive.

Understand better here:

  

What is the difference between charset and collation in database?

Short answer: it is correct and it is not, it depends on the need. In your case, dealing with username and password, it would be better to validate data entry with a specified pattern, user names are generally considered valid only with lowercase numbers and etc., following this context if you always validate the input of the data so that such a value (always tiny), you will not have to apply specific things when querying the database for example .. But if you want a user name to have uppercase characters, then that implementation would make sense . Hope it helps!

It is not incorrect but has consequences.

The BINARY keyword makes a cast of the string to a binary string, forcing the comparison byte to byte rather than character to character, making case sensitive comparison as well as making the leading spaces in the string meaningful.

If you really need the comparison for this field to be case sensitive, it is more appropriate to make the whole BINARY column, because using the BINARY keyword in the query overrides the use of the index if that column is indexed. This is bad, and in your case, this column should be indexed.

In the example below note that the xname index will not be used when using BINARY:

    mysql> explain select * from tb where xnome = 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb
   partitions: NULL
         type: ref
possible_keys: xnome
          key: xnome
      key_len: 153
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0,00 sec)

mysql> explain select * from tb where BINARY xnome = 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0,00 sec)

This does not occur when you create the column as BINARY, the index is already created properly.

You can change the column type:

ALTER TABLE nome_tabela CHANGE nome_coluna nome_coluna VARCHAR(XX) BINARY NOT NULL;